Pivot table column that calculates the delta of the arbitrarily selected two columns.

[nezuko_kamado]

How can I add a column to a pivot table (or just table) that calculates the difference in two arbitrarily selected columns?

So the expected output is as below. Any columns of two weeks are selected, then delta is calculated. 

Sum( week1 unit)- Sum(week3 unit) or sum(week2 unit) - sum(week 3 unit) and so on...

sum(Unit) -before(sum(Unit) creates  columns inside of existing column. But I want to create extra column with delta.

[vinieme12 · Accepted solution]

Create a dummy dimension in your Script

Dummy:

Load * Inline [

Dim

1

2];

To Dynamically evaluate delta you need to first capture which Weeks are Selected

vSelectedWeek1 = Subfield(Getfieldselections(Week),',',1)

vSelectedWeek2 = Subfield(Getfieldselections(Week),',',1)

Finally

in your Pivot table, Add "Dim" and "Week" as columns

Change your expression to evaluate based on Dim Value

= Pick(Dim,Sum(Unit), sum({<Week={$(vSelectedWeek1)}>}Unit) - sum({<Week={$(vSelectedWeek2)}>}Unit))