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

ratio of cumulative sums

jean0101

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

  • Re: ratio of cumulative sums
    Nicole Smith

    If you make the expression on your chart:

     

    =sum(num1/num2)

     

    instead of

     

    =n1/n2

     

    it works fine.

    • Re: ratio of cumulative sums
      jean0101

      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?

      • Re: ratio of cumulative sums
        Jagan Nalla

        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
        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.

        • Re: ratio of cumulative sums
          jean0101

          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
            Jagan Nalla

            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
              jean0101

              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?