Adding difference columns to pivot tables
I simply have a pivot table where the first column contains a varchar and the last column contains grand totals. All columns in between are monthly totals. I would like to add columns in between the monthly total columns showing the difference between the column to the left vs. the column to the right.
The only script I found was here (https://community.sisense.com/t5/knowledge/pivot-table-value-difference/ta-p/9197) and it just turns my first column to all zeros. Has anyone accomplished something like this?
TIA!
Hi danblack,
You can use the next() function for this. Here's an example formula:
( <your aggregation formula> + CASE WHEN ISNULL((<your aggregation formula>, NEXT([Months in <your date field>], 1))) THEN <your aggregation formula> ELSE (<your aggregation formula>, NEXT([Months in <your date field>], 1)) END ) / 2This formula assumes for the last month, since there is no more column to the right, the averages would be the same values as that month's column.
Here's a how the results look like with Sample ECommerce data:
-Tri
