0 Replies Latest reply: Jul 17, 2014 7:36 AM by Jan Rodenburg RSS

    rangesum(below) function and Pivot Chart - issue when selecting subs

      Dear Qlikview users,

      I want to Report on daily net growth of a client database.


      My source is: [id];[date];[event]
      [event] = activation (a) or termination(t)


      The output is in a Pivot Chart with dimension [date] with following expressions:

      Activations:

      sum( {$<EVENT={a}>} ID)

       

      Terminations:

      sum( {$<EVENT={t}>} ID)

       

      Net growth per day:

      sum( {$<EVENT={a}>} ID) - sum( {$<EVENT={t}>} ID)

       

      Net growth cumulative:

      rangesum (below(sum ({$<EVENT ={a}>} ID)-sum( {$< EVENT ={t}>} ID),1,NoOfRows(TOTAL)))

       

      Untill now it works fine.

      But the [Net growth cumulative] only works fast when having selected only a subset of [date] in the Pivot chart.

       

      When I select all (with a correct sum of whole database) the chart becomes unusable because of very long load time (history is from years back)

       

      But when selecting a subset then [Net growth cumulative] does only sums up the Activations and Terminations of days selected in the Pivot  and therefore its incorrectly shown.

       

      My questions: how to solve this problem?

      I hope the solution should be in the NoOfRows(TOTAL) part ?

      Note: it is important to be able to click and view in the charts based on ID level and not on aggregated level.

      Or maybe this is just not possible and I should try another set up?

       

      Thanks in advance for your thoughts,  answers or solutions!

       

      Jan