Cumulative Calculation

Wed, 27 Nov 2013 17:53:42 GMT

Hi All,

Thanks in advance! I have what seems to be a simple request: Show cumulative Sum(Sales)/Sum(Quantity) over time. The 'Cumulative' check box on the expression tab is not working for this and I'll show you why. I have data like this

What I want is cumulative average sales for each month. It would look like this

Month 1 C Avg Sales = 25/12 = 2.1

Month 2 C Avg Sales = (25+23) / (12+13) = 1.9

Month 3 C Avg Sales = (25+23+27) / (12+13+15) = 1.875

etc.

And for month 12 it would be 357 / 240 as the total would show.

Instead, what I get is

Month 1 Avg Sales = 25/12 = 2.1 (Good)

Month 2 Avg Sales = 23/13 = 1.76 (wrong)

Month 3 Avg Sales = 27/15 = 1.8 (wrong)

etc.

When I check the cumulative check box on the expression tab it gives me the right individual Sales and Quantity numbers that I want to use for each month BUT when I do the calculation Avg Sales [ Sum(Sales)/Sum(Quantity) ] and check cumulative, I get the current calculated Avg Sales + Last month calculated Avg Sales + Last 2nd month calculated Avg Sales, etc. In other words, it Calculates Avg Sales and then Accumulates where as what I want is to Accumulate each piece (Sales and Quantity) and then Calculate Avg Sales.

Many thanks!

Re: Cumulative Calculation

tresB — Wed, 27 Nov 2013 18:11:43 GMT

Try :

=RangeSum(Above(total sum(Sales),0,RowNo())) / RangeSum(Above(total sum(Quantity),0,RowNo()))

Re: Cumulative Calculation

Wed, 27 Nov 2013 18:18:24 GMT

That is looking sweet so far! Thanks tresesco!

topic Re: Cumulative Calculation in QlikView

Cumulative Calculation

Re: Cumulative Calculation

Re: Cumulative Calculation