Cumulative sum in chart

[#1]

Hi all,

In a pivot table I have used the following formulas for a cumulative sum of sales and target:

Formula 1 - [Ex-Factory Actual C] =If(ISNULL(Sum({<Source = {'Ex-Factory Actual'}>} Sales)+ before([Ex-Factory Actual C])),Sum({<Source = {'Ex-Factory Actual'}>} Sales),Sum({<Source = {'Ex-Factory Actual'}>} Sales)+ before([Ex-Factory Actual C]))

Formula 2 - [Ex-Factory LBE C] =If(ISNULL(Sum({<Source = {'Ex-Factory LBE'}>} Sales)+ before([Ex-Factory LBE C])),Sum({<Source = {'Ex-Factory LBE'}>} Sales),Sum({<Source = {'Ex-Factory LBE'}>} Sales)+ before([Ex-Factory LBE C]))

Formula 3 - Salves v Target cumulative % =[Ex-Factory Actual C]/[Ex-Factory LBE C]

These work perfectly in the pivot table. However, when I put these in to a chart (where 1 & 2 are bars and 3 is a line) and have 'Full Accumulation' on 1&2, 1&2 work fine but 3 does not. 3 does not do a cumulative Sales v Target (SvT), just monthly SvT. If I add Full Accumulation to 3 it simply adds together the SvT figures for each month cumulatively (so we end up with 300% after 3 months for example).

Essentially what I'm looking for is the SvT line to cumulatively add sales and target by month (flexible dependent on time period selected) and divide sales by target for the SvT in a line on the graph and for this to show for every month selected.

For example:

Sales     Target     Cumu Sales     Cumu Target     Cumu SvT           Month

90          100              90                100                     90%                    1

120        130              210               230                     91%                    2

150        100              360               330                    109%                   3

Many thanks in advance for any help.

[sunny_talwar]

I think it would be better to accumulate using RangeSum for the third expression.. also are you using this in a straight table or pivot table? What is Before even doing here? It doesn't even work in a straight table? If you are not using straight table, then where are you getting the option to do full accumulation in pivot table?

[#3 · Accepted solution]

Hi Sunny,

Thanks for responding. I've figured it out using this formula:

=rangesum(above([Ex-Factory Actual C], 0, rowno()))/rangesum(above([Ex-Factory LBE C], 0, rowno()))

I am using a pivot table. I can't use accumulation in a pivot table as you say, but I was referrig to using full accumulation in the chart.

anyway, it's all sorted now and working perfectly. Thanks for the suggestion!

[sunny_talwar]

That is great... I would suggest you to close this thread by marking your own response as correct

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny