Cumulative count with 1 start value and shifted rows

[Michiel_QV_Fan]

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.

[Anonymous]

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