Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

Thanks in advance

1 Solution

Accepted Solutions
Not applicable
Author

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

Then I don't need any accumulation in QlikView

View solution in original post

11 Replies
Nicole-Smith

If you make the expression on your chart:

=sum(num1/num2)

instead of

=n1/n2

it works fine.

Not applicable
Author

thanks for your reply but it's not what I want:

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?

jagannalla
Partner - Specialist III
Partner - Specialist III

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.

Nicole-Smith

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.

Not applicable
Author

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

jagannalla
Partner - Specialist III
Partner - Specialist III

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.

Not applicable
Author

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?

jagannalla
Partner - Specialist III
Partner - Specialist III

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.

Not applicable
Author

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?