Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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)
)
)