Looking for some advice on retrieving data from Pivot Table

[JeffGrant]

Hi All,

I am lookingfor advice or even if this possible.

In my model, I use Pivot tables and GetPivotData, no problems. And this works well, when my column headings are all unique values.

However, I am now seeing that as the data records increase, the number of unique column headings is also increasing, which means the data in the cells is effectivcely "thinning out".

If I use something like the Grouping function on the column headings, then the GetPivotData function will not work.

How can I effectively use something like the GetPivotData function but in a Grouped column environment?

Maybe and Index/Match with an approximate match instead of an exact match ????

The actual number of headings is not the problem, it is the thinning out of the data sets which is causing the drama.

I hope somebody has an idea on this one.

Thanks in advance

Jeff.

 

[GraH]

hi, not sure what thinning out means.

I would do the grouping, but collaps the pivot, or get rid of the detailed data field, or use subtotals.
Make sure your getpivotdata formula refers to this grouping field (on subtotals when these are used).
Basically keep the pivot simple. As in single dimensions in columns/rows section. Create more pivots if other combinations are required.

 

[JeffGrant]

Hi GraH,
by thining, i just mean the data is spreading out. For example, yesterdays data has row header 1000, todays row headers are 1000, 1002, 1005, 1006, 1007,

and now I have a 1 & 3 popping out to the side, where i need everything to be under the 1000 column heading, by grouping, the 1 & 3 will be where they need to be, but because they are under headings 1006 and 1007, doing a getpivotdata on column heading 1000, willnot include the 1 & 3, like wise doing a getpivotdata on column 1006, will return 1,instead of 30.

how do i do this: Make sure your getpivotdata formula refers to this grouping field (on subtotals when these are used).

 

[GraH]

Sorry I can't share trough XL2BB for the moment, so need to share a picture .

Depending on how the grouping is done (I cant guess based on the small set you shared), you can do the grouping in the data. As you see the getpivotdata refers to the "group sub-total".

 

[JeffGrant]

Thanks for that GraH. It now posses an interest question. The raw data is being drawn from Access because Excel and PQ suffer performamce issues with the amount of data I have.

Because you have grouped the data and then created a Pivot table, I will invesitgate how this can be done in SQL.

I can see it can be done, I just don't know how - yet