GETPIVOT Help

[Pearl_022]

Hi,

I am trying to calculate GETPIVOTDATA but I am not sue how I can get my result. I would like to get result in manner where I can see total batches and when I apply site filter It should work. I tried several things bit it is not working. Any help would be appreciated.


This is my pivot table

I have Year and Site as a filter and I want to make site filter work with my total batches and total rejection in another sheet (Dashboard view) when I put Getpivotdata calculation there

Row Labels	Sum of Totalbatches	Sum of TotalRejection
Jan	96​	15​
Feb	25​	2​
Mar	34​	0​
Apr	10​	2​
May	38​	4​
Jun	24​	7​
Jul	9​	0​
Aug	10​	2​
Sep	15​	9​
Oct	25​	2​
Nov	9​	0​
Dec	22​	11​
Grand Total	317​	54​

Thanks in advance.

 

[Pearl_022]

Do you remember my first reply? Quote "Why are you putting fields in a filter if you are going to want to fetch whatever result from the PivotTable? Put the Year and Site also in the Row area, and specify them in the GetPivotData function formula." ... you need to do it that way ... have a site in-cell drop-down in your dashboard, and use that cell value within GETPIVOTDATA functions to fetch the required data. You also say "they dont want to put year filter on dashboard view" ... so how will you know what dates to use? Will you be calculating the date(s) somehow? If so, those calculations can also be used in the GETPIVOTDATA functions, to control which months to fetch. I'm imagining you'd need 12 hidden columns or rows to do this work in .... do you see what I mean?

Yes. My calculation for MTD and YTD is working without GETPIVOTDATA condition. I am using calculation SUMPRODUCT((YEAR(Sheet1!$B$1)<=YEAR(Data!$B$2:$B$40))*(Data!$A$2:$A$40='Total Batches'!$B$1)*(Data!$G$2:$G$40)) for MTD and YTD. But I am stuck with QTD and 2019 YTD calculation. Can you please advise. It will be great help, I missed my deliverable as I am stuck with QTD and 2019 YTD calculation.

 

[GlennUK]

Surely it's just a matter of working out the start and end dates of the quarter and putting comparisons for those in the formula vs Data column B (for QTD)? Likewise, for 2019 YTD just work out start and end dates of the window you want in the same way?

 

[Pearl_022]

Surely it's just a matter of working out the start and end dates of the quarter and putting comparisons for those in the formula vs Data column B (for QTD)? Likewise, for 2019 YTD just work out start and end dates of the window you want in the same way?

In my data I have date formate like column B only. I am sorry if I am not understanding your question correctly.

 

[GlennUK]

How is it that you know how to create that SUMPRODUCT formula, but can't see that it will work when you adjust it the way I said?