Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have created a pivot table with 2 periods and want to add Variance column to show difference between previous period vs current period.
I have created a set analysis formula like below, but I do not get the correct result.
=(Sum({<[ACCTG DATE (P0045)]={$(=max([ACCTG DATE (P0045)]))}>}[FUNCTIONAL AMT(YTD)])
- Sum({<[ACCTG DATE (P0045)]={$(=min([ACCTG DATE (P0045)]))}>}[FUNCTIONAL AMT(YTD)]))
The result I want is
Function | Function | Var | |
ACCTG DATE (P0045) | 20191231 | 20200131 | 20200131 vs 20191231 |
Status | 20,191,231 | 20,200,131 | 8,900 |
Totals | (19,510,795) | (19,304,099) | 206,696 |
Exist | (19,510,795) | (19,304,099) | 206,696 |
But I get an incorrect result like below. Hope I can get some guidance
Function | Function | Var | Var | |
ACCTG DATE (P0045) | 20191231 | 20200131 | 20191231 | 20200131 |
Status | 20,191,231 | 20,200,131 | 20,191,231 | 20,200,131 |
Exist | (19,510,795) | (19,304,099) | 19,510,795 | (19,304,099) |
Thank you very much. Do you mean modifying the script in Script editor? Or I need to remove some of the fields in the left hand side of the pivot that were collapsed ?