cumulative totals in pivot table when using nested variables

tracysmart — Wed, 26 Aug 2015 10:33:38 GMT

Hi everyone

this has had me scratching my head for a while now so i thought i would ask my question to you my peers.

I am trying to do a cumulative total within a pivot table which in theory should be straightforward.

I want column %Cumulative to be the cumulative total of the row above/column before %DemandCovered

so for %Cumulative column one it would be 166.37

%Cumulative column 2 it would be 166.37 + 151.22

%Cumulative column 3 would be 166.37 + 151.22 + -11.57 (These percentages are not quite right in themselves but that is another issue involving the data and calculation!)

I have been trying using the rangesum function, i have also experimented with both ABOVE and BEFORE but i am going round in circles with this one.

I am wondering if the layout of the pivot table, which is how it is needed, and the fact i am nesting variables in my expressions is making this more difficult than it should be

Any advice/help woud be greatly appreciated

Tracy

Re: cumulative totals in pivot table when using nested variables

tracysmart — Thu, 10 Sep 2015 12:42:18 GMT

After a few weeks off this, i came back to it this today and could see clearer!

This is how my expression ended up

//The rangesum fuction is passing itself and the cumulative total from the column before
RangeSum( $(vDemandCoveredPercent), before(total $(vDemandCoveredPercent),1,ColumnNo(TOTAL)))

topic Re: cumulative totals in pivot table when using nested variables in QlikView

cumulative totals in pivot table when using nested variables

Re: cumulative totals in pivot table when using nested variables