topic Re: Cumulative Sum - Total in QlikView

Cumulative Sum - Total

Tue, 11 Sep 2012 11:21:27 GMT

I have a situation where I would like to calculate the cumulative sum of a data set and demonstrate it on a pivot table.

The cumulative sum is being calculated using RangeSum, however in a pivot table, the Total's are not being populated.

Is there any way I can see the totals being shown with the last cumulative data

The QVW is attached for your reference

Thanks and Regards,

Sulaiman

Jason_Michaelides — Tue, 11 Sep 2012 13:59:03 GMT

You could use Dimensionality():

If(Dimensionality()<2,Sum(Amount),

RangeSum(Above(Total Sum([Amount]),0,RowNo(TOTAL))))

Hope this helps,

Jason

Tue, 11 Sep 2012 14:03:33 GMT

Thanks for the reply. The answer to that on the table is 40 for product A and 110 for product B.

As per the image attached the total for Product B needs to be 150 ( 110 + 40 (from Product A) )

Any idea how ??

Thanks and regards,

Jason_Michaelides — Tue, 11 Sep 2012 14:28:04 GMT

My bad:

If(Dimensionality()=1

,Aggr(RangeSum(Above(Sum([Amount]),0,RowNo(TOTAL))),Product)

,IF(Dimensionality()=0

,Sum(Amount)

,RangeSum(Above(Total Sum([Amount]),0,RowNo(TOTAL)))))

Wed, 12 Sep 2012 05:44:12 GMT

Works like a charm... Thanks a lot Jason : )

Jason_Michaelides — Wed, 12 Sep 2012 06:23:41 GMT

No problem. Please mark a correct answer so the thread is closed.