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.

 

[GlennUK]

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.

 

[Pearl_022]

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.

Hi Glenn,

Thanks for your respond. Actually I created dashboard where I want to show value according to filter selection in MTD, YTD and QTD. Attached screenshot for your reference. I tried doing calculation for MTD, YTD and QTD but not working as expected.

 

[GlennUK]

Can you show a picture of your raw data ... just a few rows to see fields and typical contents ... and a picture of your PivotTable, showing the filter fields, and also the PivotTable Field List area. Can you also show and explain what formulas you are using for your calculations for MTD, YTD, and QTD.

 

[Pearl_022]

Can you show a picture of your raw data ... just a few rows to see fields and typical contents ... and a picture of your PivotTable, showing the filter fields, and also the PivotTable Field List area. Can you also show and explain what formulas you are using for your calculations for MTD, YTD, and QTD.

Hi Glenn,

My calculations for YTD, MTD and QTD is not working as expected. Do you thing you can help me with that ? (I have data from dec 2019 to dec 2020. Requirement is in report they want 2019 YTD, YTD 2020, MTD for only month of december and QTD is for Oct- Dec 2020. I am stuck in calculation I am able to do normal calculation but with that calculation when I change site filter that value doesn't change for site.) Below is the data.

Site	Date	Month	Year	Total batches	Total Rejection
Texas	12/1/19​	12​	2019​	4​	0​
Boston	12/1/19​	12​	2019​	5​	4​
Chicago	12/1/19​	12​	2019​	6​	0​
New Jersey	12/1/19​	12​	2019​	1​	4​
Chicago	1/1/20​	1​	2020​	90​	7​
New Jersey	1/1/20​	1​	2020​	6​	5​
New York	1/1/20​	1​	2020​	0​	3​
Philadelphia	2/1/20​	2​	2020​	10​	0​
Maryland	2/1/20​	2​	2020​	15​	2​
Delawar	3/1/20​	3​	2020​	30​	0​
Texas	3/1/20​	3​	2020​	4​	0​
Boston	4/1/20​	4​	2020​	3​	0​
Chicago	4/1/20​	4​	2020​	7​	2​
New Jersey	5/1/20​	5​	2020​	8​	3​
New York	5/1/20​	5​	2020​	30​	1​
Philadelphia	6/1/20​	6​	2020​	15​	5​
Ohio	6/1/20​	6​	2020​	2​	0​
Maryland	6/1/20​	6​	2020​	7​	2​
Delawar	7/1/20​	7​	2020​	5​	0​
Texas	7/1/20​	7​	2020​	4​	0​
Boston	8/1/20​	8​	2020​	3​	0​
Chicago	8/1/20​	8​	2020​	7​	2​
New Jersey	9/1/20​	9​	2020​	8​	3​
New York	9/1/20​	9​	2020​	2​	1​
Philadelphia	9/1/20​	9​	2020​	5​	5​
Ohio	10/1/20​	10​	2020​	10​	0​
Maryland	10/1/20​	10​	2020​	15​	2​
Delawar	11/1/20​	11​	2020​	5​	0​
Texas	11/1/20​	11​	2020​	4​	0​
Boston	12/1/20​	12​	2020​	3​	0​
Chicago	12/1/20​	12​	2020​	7​	2​
New Jersey	12/1/20​	12​	2020​	8​	3​
New York	12/1/20​	12​	2020​	3​	1​
Philadelphia	12/1/20​	12​	2020​	1​	5​

Appreciate your help.

 

[GlennUK]

Out of the 4 things I asked for, you've provided one ... I don't have enough information to answer your query.

 

[Pearl_022]

Out of the 4 things I asked for, you've provided one ... I don't have enough information to answer your query.

I am sorry I miss read your earlier message. I have attached screenshot of pivot field and table. I tried below calculation for YTD. My YTD calculation is not working after calculation So I didnt try MTD and QTD calculation.

YTD : =(SUM(GETPIVOTDATA("Sum of Total Batch", total Batch!A4))
Also I tried but this is not giving me the result =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))

I also have attached sheet1 formate which I need to use to do calculation.

 

[GlennUK]

I don't understand what you are aiming for with the GETPIVOTDATA attempt ... it makes no sense to me (especially why you put a SUM round it). But the SUMPRODUCT looks promising ... although, again, I don't think it makes sense .... you're referring to Sheet1!$B$1 ... but are putting YEAR around it? Isn't it a 4 digit number, not a date? And if you want to select a particular year, why are you messing with the date in the data, when there is an actual YEAR field in there?

 

[Pearl_022]

Hi Glenn,

I know but my client only want to see site filter on dashboard page and whatever site they select MTD, YTD and QTD needs to change they dont want to put year filter on dashboard view. I dont know much about advance excel. This is totally new for me so I am trying to explore option.

 

[GlennUK]

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?