topic Re: Rolling Sum in QlikView

Rolling Sum

Anonymous — Wed, 21 Nov 2018 16:26:11 GMT

I have a table with two columns. They are MONTHYEAR (dimension) and a measure, which is really a variable, called TTF_13. I'm trying to add a third column which will be a 12-month sum for each row. It is not a rolling sum only of what is displaying in the table, it should be the sum of the prior 12 months up until the month pertaining to the current row. I've tried both of these approaches but am having no success:

RangeSum(Above(Sum({<MonthYear>}TTF_13),0,12))

and

Sum({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -12)))<=$(=Date(Max(MonthYear)))"}>}TTF_13)

In each case, only zeroes are displaying. Any ideas on what I'm doing wrong?

Note: This is actually a chart object where the chart type is a straight table.

Thank you so much for your time!

Re: Rolling Sum

Gysbert_Wassenaar — Sun, 25 Nov 2018 16:30:05 GMT

Perhaps this documents helps: https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-other/ta-p/1483033

Re: Rolling Sum

BI — Mon, 26 Nov 2018 06:33:50 GMT

HI,
It depends on the variable value, if it is straight field then can use below Expression
RangeSum(Above(Sum({<MonthYear>} $(TTF_13)),0,12))

Re: Rolling Sum

Anonymous — Mon, 26 Nov 2018 13:47:56 GMT

Thanks for the suggestion. Your suggestion simply results in a dash being displayed for each row. However, if I do this:

RangeSum(Above(Sum({<MonthYear>} TTF_13),0,12))

...it results in zeroes. Any other thoughts?

Re: Rolling Sum

Anonymous — Mon, 26 Nov 2018 13:49:07 GMT

Yeah, I read that which lead me to try this:

RangeSum(Above(Sum({<MonthYear>}TTF_13),0,12))

...but it's not working as expected.

Re: Rolling Sum

Gysbert_Wassenaar — Mon, 26 Nov 2018 17:32:29 GMT

As I explained in the document I linked to the rangesum(above(...)...) construction can only calculate with the data shown in your chart/table. You want to calculate using data that's not shown in the table as well. And therefore you need to use one of the other two options. The sum(aggr(rangesum(above(...)...), MonthYear)) variant or the As-Of table.