Pivot table formula

[Noxqss38242]

2021 YTD Data.cost review.Jan21.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Reason Code	(Multiple Items)
2	Vend / Term	VEND
3
4	Sum of Task	Column Labels
5	Row Labels	13 Jan 20	14 Feb 20	15 Mar 20	16 Apr 20	17 May 20	18 Jun 20	19 Jul 20	20 Aug 20	21 Sep 20	22 Oct 20	23 Nov 20	24 Dec 20	Inc/Dec from last month	24 Dec 20
6	TIRES	$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)
7	BRKTRL	$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
8	PWRUNT	$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
9	SUSPEN	$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
10	BODY	$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
Range	Formula
N6	N6	=GETPIVOTDATA("Task Total",$A$4,"Month","24 Dec 20","System Text","TIRES")-GETPIVOTDATA("Task Total",$A$4,"Month","23 Nov 20","System Text","TIRES")
N7	N7	=GETPIVOTDATA("Task Total",$A$4,"Month","24 Dec 20","System Text","BRKTRL")-GETPIVOTDATA("Task Total",$A$4,"Month","23 Nov 20","System Text","BRKTRL")
N8	N8	=GETPIVOTDATA("Task Total",$A$4,"Month","24 Dec 20","System Text","PWRUNT")-GETPIVOTDATA("Task Total",$A$4,"Month","23 Nov 20","System Text","PWRUNT")
N9	N9	=GETPIVOTDATA("Task Total",$A$4,"Month","24 Dec 20","System Text","SUSPEN")-GETPIVOTDATA("Task Total",$A$4,"Month","23 Nov 20","System Text","SUSPEN")
N10	N10	=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
Cell	Allow	Criteria
O5	List	=$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...

 

[Alex Blakenburg]

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]

=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]

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]

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:-

=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)

=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))

 

[Noxqss38242]

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:-

=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)

=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]

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.