Pivot Table - Get Change Values Compared to Previous Interval

[lb654]

Hi,

I'd like some help with Pivot Tables in Qlik Sense. I'm trying to build a (aggregated) monthly revenue table for different departments where each month column also displays the revenue changes compared to the previous month. Currently, I have the following:

Each department is represented by a separate row, the months include the monthly revenue, and the absolute changes compared to the previous month (Delta, Δ column) .

The above solution works with the Before()-method to check the cell value of the previous month - e.g., the Delta column is defined as Revenue - Before(Revenue). Now while this works fine when the full table is displayed, as soon as a (non-sequential) selection of months is made, the before()-method simply returns the value of the cell to the left. This is technically expected, but of course, the delta values are incorrect from a business perspective:

The 2009-Nov Delta column shows the changes from 2007-Jan to 2009-Nov, while I want it to still show the changes from 2009-Oct to 2009-Nov.

So what I'd like to get is a replacement for the Before()-method, something like

Delta = Revenue - Sum( {<department={this.department}, month={AddMonths(this.month, -1)}>} totalRevenue)

I tried several attempts as well as searching for different keywords, but didn't get it to work. I appreciate your help!

[Spivey]

I think this will get you most of the way there - Use this as the Delta measure:

=sum(
    aggr({<TransMonth=>} 
        Sum(Expression1) - Above(Sum(Expression1))
      , ( Department , Text, Descending)
      , ( TransMonth , Numeric, Descending)
    )
)

 ... where TransMonth is the month field and Sum(Expression1) is the revenue metric. Note that you can't use a field-on-the-fly as the month field -- create the month field in the load script rather than using something like Month([dateField]) in the frontend.

[lorevanachter]

Late on this issue, here's what I created for a similar case, using the reply of @Spivey  I was able to make it work.
For my case, I needed the current value to be outside the aggr for it to work.

Hope this helps you or future developers.

=
Sum(Expression1) - 

sum(
    aggr({<TransMonth=>} 
        Above(Sum(Expression1))
      , ( Department , Text, Descending)
      , ( TransMonth , Numeric, Descending)
    )
)