I have a table with Calendar Month on the first column and totals on the second column. How can I calculate a rangesum() on the first row of the table, when I need "above()" to see the total that is prior to the first row.
Example:
Rolling 2-mt Total = RangeSum(Above($(=vTotalRev),0,2))
In Data set: 112018 400 122018 400 012019 200 022019 300
In Qlik Table (table needs to start from 122018)
122018 400 400 ??? ( Needs to be 800 ) 012019 200 600 022019 300 500
I guess Inside the above you can have the aggr() function which will contain all the monthyear and then you can have them rangesum to get the rolling-2 month total
I am having the issue when calculating a rolling 2 months sum or avg using ABOVE(). I am attaching the qvf file. The min(YearDate) needs to be excluded from the charts, but the following month(i.e: 2018-01) must include 2017-12 on the scope of calculation for RANGESUM(ABOVE([Sales],0,2)), so I can get a 2 month running total on the first row on the table (2018-01 in this example)
I've tried this: If I null() YearMonth on the first month, I can uncheck "include nulls values" on the table/chart, but this affects ABOVE() since 2017-12 is no longer visible. You can see the TOTAL qualifier to the expression.
A similar issue is if I click to filter on a YearMonth column. ABOVE() seems to lose visibility of the previous months on the data set. Even if you add the TOTAL qualifier to the expression. I appreciate in advance for any help on this.
Rolling Totals Using ABOVE(), RANGESUM, RANGEAVG() - Issue with the first month.qvf
