Pivot table formula

Noxqss38242

Board Regular
Joined
Sep 15, 2017
Messages
137
Office Version
  1. 2016
2021 YTD Data.cost review.Jan21.xlsx
ABCDEFGHIJKLMNO
1Reason Code(Multiple Items)
2Vend / TermVEND
3
4Sum of TaskColumn Labels
5Row Labels13 Jan 2014 Feb 2015 Mar 2016 Apr 2017 May 2018 Jun 2019 Jul 2020 Aug 2021 Sep 2022 Oct 2023 Nov 2024 Dec 20Inc/Dec from last month24 Dec 20
6TIRES$85,635$73,040$105,563$111,593$114,126$136,988$148,968$158,450$147,167$114,371$103,910$90,026($13,883)
7BRKTRL$37,261$35,302$25,062$26,241$28,392$31,672$38,044$29,502$28,424$28,687$19,176$21,956$2,780
8PWRUNT$29,764$20,188$27,415$19,461$24,794$17,567$20,952$27,785$24,457$25,748$4,080$22,922$18,842
9SUSPEN$21,960$32,699$24,052$26,763$14,342$17,974$20,638$25,269$24,497$19,931$14,807$15,763$956
10BODY$23,922$18,502$19,765$15,655$15,189$16,869$17,992$15,840$20,034$13,636$12,218$21,158$8,940
Vend $ Trend by System
Cell Formulas
RangeFormula
N6N6=GETPIVOTDATA("Task Total",$A$4,"Month","24 Dec 20","System Text","TIRES")-GETPIVOTDATA("Task Total",$A$4,"Month","23 Nov 20","System Text","TIRES")
N7N7=GETPIVOTDATA("Task Total",$A$4,"Month","24 Dec 20","System Text","BRKTRL")-GETPIVOTDATA("Task Total",$A$4,"Month","23 Nov 20","System Text","BRKTRL")
N8N8=GETPIVOTDATA("Task Total",$A$4,"Month","24 Dec 20","System Text","PWRUNT")-GETPIVOTDATA("Task Total",$A$4,"Month","23 Nov 20","System Text","PWRUNT")
N9N9=GETPIVOTDATA("Task Total",$A$4,"Month","24 Dec 20","System Text","SUSPEN")-GETPIVOTDATA("Task Total",$A$4,"Month","23 Nov 20","System Text","SUSPEN")
N10N10=GETPIVOTDATA("Task Total",$A$4,"Month","24 Dec 20","System Text","BODY")-GETPIVOTDATA("Task Total",$A$4,"Month","23 Nov 20","System Text","BODY")
Cells with Data Validation
CellAllowCriteria
O5List=$B$5:$M$5


What I'm wanting is to use the drop down list on cell O5 with the pivot table.
Select which month using the drop down list, and the formula pull info and fill in O6 and down
The data I want to see is the difference from the selected month from the drop down vs. the previous month.

IE: drop down saying 16 April 20 and the data pull
Tires April data of $111,593 - Tires March data of $105,563 and provide the answer which should be $6,030
If I chose December Data:
Tires December data of $90,026 - November data of $103,910 and it provide the answer which should be ($13,883)

This again would pull all the data for each field A6 "TIRES", A7 "BRKTRL", etc...filled out starting in O6, O7, O8, etc...
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
572
Office Version
  1. 365
Platform
  1. Windows
Are you planning on leaving the formulas next to the pivot table like you have ?
If you are I am not sure I can see the benefit of using Pivot Table formulas, you may as well use standard lookup formulas such as Xlookup or Index Match.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Excel Formula:
=IFERROR(GETPIVOTDATA("Task Total",$A$4,"System Text",A6,"Month",$O$5),0)-IFERROR(GETPIVOTDATA("Task Total",$A$4,"System Text",A6,"Month",TEXT(DATE(YEAR($O$5),MONTH($O$5)-1,DAY($O$5)),"d-mmm")),0)
 

Noxqss38242

Board Regular
Joined
Sep 15, 2017
Messages
137
Office Version
  1. 2016
Are you planning on leaving the formulas next to the pivot table like you have ?
If you are I am not sure I can see the benefit of using Pivot Table formulas, you may as well use standard lookup formulas such as Xlookup or Index Match.

No, those formulas will go away. I have to keep changing them one by one each time I want to look at a month and is time consuming.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
572
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The issue as I see it is how to calculate the date of the previous month. What I am seeing is that the day in the Date seems to be unusual in that its incrementing each month ie Oct is 22 Nov is 23 Dec is 24.
Is this a trend that is going to continue (it seems unlikely) ?
If you can calculate the prior month somehow then you can simply use mart37's formula and tweak it if you need to. It currently seems to assume the day will be the same each month (calculated the Nov date as being Nov 24, your table indicates it should be Nov 23.
If you can't calculate it you could use a lookup table to look up the prior month month date and modify mart37 with a lookup value for the prior month date. This will mean setting up a lookup table.

You could use a simple index match and then get the prior month based on finding the current date and then subtracting 1 from the column no to get the prior month but you would then need to work out how you make sure the lookup range expands to include additional columns as the pivot table expands to the right. Mind you if you have formulas to the right of the table you are going to have to keep inserting a column each time before you run it anyway, so you can factor that into the formula's range.

Sample index match:-
Excel Formula:
=INDEX($A6:$M6,0,MATCH(O$5,$A$5:$M$5,0))-INDEX($A6:$M6,0,MATCH(O$5,$A$5:$M$5,0)-1)

Similar to mart37 but with an index match for the prior month (and would therefore also need to cater for more columns)
Excel Formula:
=GETPIVOTDATA("Task Total",$A$4,"System Text","TIRES","Month",O$5)-GETPIVOTDATA("Task Total",$A$4,"System Text","TIRES","Month",INDEX($A$5:$M$5,0,MATCH(O$5,$A$5:$M$5,0)-1))
 
Solution

Noxqss38242

Board Regular
Joined
Sep 15, 2017
Messages
137
Office Version
  1. 2016
The issue as I see it is how to calculate the date of the previous month. What I am seeing is that the day in the Date seems to be unusual in that its incrementing each month ie Oct is 22 Nov is 23 Dec is 24.
Is this a trend that is going to continue (it seems unlikely) ?
If you can calculate the prior month somehow then you can simply use mart37's formula and tweak it if you need to. It currently seems to assume the day will be the same each month (calculated the Nov date as being Nov 24, your table indicates it should be Nov 23.
If you can't calculate it you could use a lookup table to look up the prior month month date and modify mart37 with a lookup value for the prior month date. This will mean setting up a lookup table.

You could use a simple index match and then get the prior month based on finding the current date and then subtracting 1 from the column no to get the prior month but you would then need to work out how you make sure the lookup range expands to include additional columns as the pivot table expands to the right. Mind you if you have formulas to the right of the table you are going to have to keep inserting a column each time before you run it anyway, so you can factor that into the formula's range.

Sample index match:-
Excel Formula:
=INDEX($A6:$M6,0,MATCH(O$5,$A$5:$M$5,0))-INDEX($A6:$M6,0,MATCH(O$5,$A$5:$M$5,0)-1)

Similar to mart37 but with an index match for the prior month (and would therefore also need to cater for more columns)
Excel Formula:
=GETPIVOTDATA("Task Total",$A$4,"System Text","TIRES","Month",O$5)-GETPIVOTDATA("Task Total",$A$4,"System Text","TIRES","Month",INDEX($A$5:$M$5,0,MATCH(O$5,$A$5:$M$5,0)-1))

Mart37 formula only returned "0".

"Sample index match" formula works.

"Similar to mart37" formula comes back as #REF!

To answer previous question on why 14 FEB 20, 15 MAR 20, etc... the raw data has all days/dates/years, to put them in order from when we started tracking, he is counting first month as 1, second month as 2 and for some reason if we don't, pivot table wasn't distinguishing or placing them in order. He built it, I'm trying to get it to do what he wants from there.

Thanks!
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
572
Office Version
  1. 365
Platform
  1. Windows
Mart37 formula only returned "0".

"Sample index match" formula works.

"Similar to mart37" formula comes back as #REF!

To answer previous question on why 14 FEB 20, 15 MAR 20, etc... the raw data has all days/dates/years, to put them in order from when we started tracking, he is counting first month as 1, second month as 2 and for some reason if we don't, pivot table wasn't distinguishing or placing them in order. He built it, I'm trying to get it to do what he wants from there.

Thanks!

If you want to pursue the GetPivotData option, the main consideration are going to be around the date field which can be a be a bit tricky. Considerations include:
• When you say it includes all dates, are they all either the 1st of the month or End of the month dates or are they transaction dates which can be any date.
• If transaction date consider having a helper column that uses =EOMONTH(B2,0), assuming you use a calendar month for your fiscal month.

If the month field used in the pivot table is based on an Excel recognised date field and you tell the pivot table to sort it, you shouldn't need to use the month pre-fix that is you inherited. The inability to sort the Month in the pivot table indicates that it is seeing it as text.
• Is the date with the prefix a helper column in the data or has the field name been manually changed directly in the pivot table ?
Also being a real date and always the 1st of the month or EOM it should be a simple matter to calculate the prior period date.

Don't worry about answering the above, they are just things you need to work through if you want to follow through on using GetPivotData.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,569
Messages
5,637,098
Members
416,957
Latest member
Brovashift

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top