Rolling sum pivot/straight table problem

[phoenix]

I have the below pivot table for calculating sum of Amount for each month_end and also calculate rolling 12 months sum of amount.

formula used:

sum(amount)

sum(aggr(rangesum(above(total sum({<RPT_PRD_DATE_ID=>} amount),0,12)),RPT_PRD_DATE_ID))

Formula for rolling 12 months seems to work perfectly except for the first few months where sum(amount) = 0; for these months it is giving wrong rolling sum.

Ex: 20080630 for this month sum(amount) = 0 and since there are no prior month end I expect to see rolling sum to be '0' too. But for some reason for this month is adding up the amounts for 20161231 - 20160131 (which are the last 12 month end dates in my data).

Need help on fixing this issue.

Thanks

[sunny_talwar · Accepted solution]

May be this:

If(Sum(amount) > 0, Sum(Aggr(RangeSum(Above(TOTAL Sum({<RPT_PRD_DATE_ID=>} amount), 0, 12)),RPT_PRD_DATE_ID)))

or

Sum(Aggr(RangeSum(Above(TOTAL Sum({<RPT_PRD_DATE_ID=>} amount),0,12)),RPT_PRD_DATE_ID)) * Avg(1)

Having said that, do you really need Sum(Aggr()) Here? May be this

If(Sum(amount) > 0, RangeSum(Above(TOTAL Sum({<RPT_PRD_DATE_ID=>} amount), 0, 12)))

or

RangeSum(Above(TOTAL Sum({<RPT_PRD_DATE_ID=>} amount), 0, 12)) * Avg(1)

Finally, why do you need a TOTAL? You only have one dimension.... I don't think it adds any value. So, try this

If(Sum(amount) > 0, RangeSum(Above(Sum({<RPT_PRD_DATE_ID=>} amount), 0, 12)))

or

RangeSum(Above(Sum({<RPT_PRD_DATE_ID=>} amount), 0, 12)) * Avg(1)

[phoenix]

Thanks stalwar1 I have used this - If(Sum(amount) > 0, RangeSum(Above(Sum({<RPT_PRD_DATE_ID=>} amount), 0, 12))), but the problem is when I sort the rpt_prd_date_id to descending it will change the rolling sum since we are using above logic here. I would like to know if there is any way to calculate the rolling sum based on the date values for the past 12 months instead of using above() function. Any suggestions?Thanks!

[sunny_talwar]

You can use Below() instead of Above

If(Sum(amount) > 0, RangeSum(Below(Sum({<RPT_PRD_DATE_ID=>} amount), 0, 12)))

[phoenix]

Thanks!

[sunny_talwar]

Great!!!

Please close the thread if you got what you wanted.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny