topic Re: Rangesum moving annual total in QlikView

Rangesum moving annual total

stubarnes — Wed, 23 Jan 2019 11:27:59 GMT

Hi, I have the following expression to calculate a rolling 12 months in a date dimension.

RangeSum(above(Sum({$<REPORTINGDATE=>}GEP),0, 12))

However, I want the sum to ignore the dimension selection but only display the selected dimension

i.e. if Jan 2018 is selected I only want Jan 2018 displayed in the chart but the calculation should be for Feb 2017 - Jan 2018

Hope that makes sense?

sunny_talwar — Wed, 23 Jan 2019 11:40:57 GMT

Try this

RangeSum(Above(Sum({$<REPORTINGDATE>} GEP), 0, 12)) * Avg(1)

stubarnes — Wed, 23 Jan 2019 12:03:56 GMT

Thank you for the solution.
Could I trouble you for a brief explanation of what the addition of * Avg(1) is doing as I don't understand?

sunny_talwar — Wed, 23 Jan 2019 12:22:33 GMT

Avg(1) = 1 for the dates/periods selected and 0 for all things not selected.... multiplying with 0 forces the non selected dates/periods to be 0 🙂