Cumulative count with 1 start value and shifted rows

Michiel_QV_Fan — Thu, 27 Dec 2012 10:44:20 GMT

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

Anonymous — Thu, 27 Dec 2012 11:46:57 GMT

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

topic Cumulative count with 1 start value and shifted rows in QlikView

Cumulative count with 1 start value and shifted rows

Re: Cumulative count with 1 start value and shifted rows