topic Re: Cumulative sum in chart in QlikView

Cumulative sum in chart

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Cumulative sum in chart

sunny_talwar — Thu, 09 Mar 2017 09:57:25 GMT

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?

Re: Cumulative sum in chart

Fri, 10 Mar 2017 10:24:55 GMT

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!

Re: Cumulative sum in chart

sunny_talwar — Fri, 10 Mar 2017 11:33:30 GMT

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