topic Re: Rolling sum pivot/straight table problem in QlikView

Rolling sum pivot/straight table problem

phoenix — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Rolling sum pivot/straight table problem

sunny_talwar — Tue, 31 Jan 2017 02:37:11 GMT

May be this:

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

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)))

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)))

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

Re: Rolling sum pivot/straight table problem

phoenix — Tue, 31 Jan 2017 03:25:08 GMT

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!

Re: Rolling sum pivot/straight table problem

sunny_talwar — Tue, 31 Jan 2017 03:30:16 GMT

You can use Below() instead of Above

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

Re: Rolling sum pivot/straight table problem

phoenix — Tue, 31 Jan 2017 03:41:37 GMT

Thanks!

Re: Rolling sum pivot/straight table problem

sunny_talwar — Tue, 31 Jan 2017 10:19:43 GMT

Great!!!

Please close the thread if you got what you wanted.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny