11 Replies Latest reply: May 1, 2012 7:38 AM by jean0101

# ratio of cumulative sums

Hello

I'm trying to divide 2 cumulatives sums but I can't get the correct result

I tried Full Accumulation, RangeSum options, etc,

Can you help me on this

• ###### Re: ratio of cumulative sums

If you make the expression on your chart:

=sum(num1/num2)

=n1/n2

it works fine.

• ###### Re: ratio of cumulative sums

for the 1st values of 09:00 indeed it's correct:

- 4654 / 23  = 202 for po

- 1500 / 3 = 500 for lo

but not for 09:15, it shows:

- 158 instead of 172 for po

- 1300 instead of 820 for lo

any other idea?

• ###### Re: ratio of cumulative sums

You are right. If we change the chart to straight table it shows different values. Let see qlikview gurus will explain how it is working actually.

• ###### Re: ratio of cumulative sums

I was able to get the correct numbers to display, but the dimensions aren't in the order that you would like them to be (attached).  I'm still not quite sure what to do in order to have the dimensions look correct.

• ###### Re: ratio of cumulative sums

thanks nicole

that's the thing I would like 1 serie for each value of c2 showing the ratio of these cumulated sums evolving in time (x axis)

I tried for few hours others ways including changing my source data output unfortunately could never get the wanted result

• ###### Re: ratio of cumulative sums

Hello,

See the attached file. It is all depends on Accumulation concept.

In expression we have Accumulation Tab. If we check Full accumulation for combo chart, or bar chart or line chart it takes the value of previous data of expr per  dimension and adds for current expr per dimension value( i.e. 2nd dim value ). This will not happen in straight table or pivot table. To overcome this we need to check No Accumulation in Accumulation tab for graphic charts.

Hope it helps you.

• ###### Re: ratio of cumulative sums

thanks jagan

Unfortunately still not what I want! I don't have a clear picture of how these rangesum, etc. work

In what I sent I also tried to hack something using 3 expressions:

- sum(num1) as n1 w/ full accumulation, hidden and with correct numbers as shown on graph sum(num1)

- sum(num2) as n2 w/ full accumulation, hidden and with correct numbers as shown on graph sum(num2)

- n1 / n2 with no accumulation on graph n2

but it's also incorrect!

Any other idea?

• ###### Re: ratio of cumulative sums

I didn't get what you are saying. According to your requirement i.e. what you asked us in previous posts i have given the solution through the file which i attached. But can explain clearly what you want? So that it helps us to understand clearly.

• ###### Re: ratio of cumulative sums

well I expected different values for 09:15 on your graph named avg:

it shows :

- 157.82 instead of ~172  = 12545 / 73 for po

- 1300 instead of 820  = 4100 / 5 for lo

Is it still unclear?

• ###### Re: ratio of cumulative sums

Any other idea?

• ###### Re: ratio of cumulative sums

I've solved this by doing the cumulative sums in SQL in another stored procedure

Then I don't need any accumulation in QlikView