Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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