1 Reply Latest reply: Dec 27, 2012 6:48 AM by Jonathan Brough RSS

    Cumulative count with 1 start value and shifted rows

    Michiel van de Goor

      I have the following challenge:

       

      I have a data set that contains:

       

      Date

      filenumber

      Status

       

      The date = only the first of the month: 20120101, 20120201 etc.

      Filenumber = filenumbers that have to be counted per Date

      Status = Running, New, Cancelled.

       

      The Running Status has 15034 Filenumbers at 20120101.

       

      I need to calculate the new Running quantity for each month start:

      20120101     15034

      20120201     15140     = 15034 + New (387) - Cancelled (282)

      20120301     11613     = 15140 + New (131) - Cancelled (3658)

      etc.

       

      In the GUI the user can selected any date, for instance 20120301.

      He/she has to see:

      Running      11613

      New              131

      Cancelled     3658

       

      To make it even more challeging we need to shift the New and Cancelled numbers 1 row up. So they are displayed on the line of their previous month.

      20120101     15034     387     282     15140

      20120201     15140     131     3658     11613

      20120301     11613     ..          ..          ..

       

       

      Questions:

      Can this all be achieved in just the dashboard?

      If so, how?

       

      Can this be calculated in the script, (with the use of intermediate tables if necessary).

      If so, how?

       

      Thanks very much.

        • Re: Cumulative count with 1 start value and shifted rows
          Jonathan Brough

          Can this all be achieved in just the dashboard?

          Yes, with month as a table chart's dimension and the expression being something like the following:

           

          sum(Qty)+above(sum(if(Status='New',Qty))-above(sum(if(Status='Cancelled',Qty))

           

          ..... the above() function looks at the next row up

           

          This can also be done in the script, by reloading the transaction table into a smaller summary table (or even as rows within the same table) and applying a Group By clause.

          I would advise doing in the charts though as grouping in the script means you lose the ability to see results at a finer level of granularity, unless you allow for the possible dimensions in the group by clause.

          Jonathan