Accumulation in time series

Tue, 07 Jun 2016 16:59:48 GMT

Hello everyone,

seeking for help/advice for accumulation in time series.

The topic was discussed here (excellent advice how to do this in Qliksense) Accumulation in Qlik Sense

I have slightly different use case and have made a solution, though I want to ask if there is a better way to do this.

I have table of orders and their statutes:

OrderID

OrderDate

ShipmentDate

Status

very small sample table (Excel)

Status can be Completed or Cancelled (i.e. closed order) as well WIP (i.e. being processed).

I want to understand how many orders in this month were closed (completed or cancelled) + all WIP (being processed).

Formula is:

Order monthly log = Closed + WIP (accumulated in this month and all from previous months)

For closed items I think correct set expression is:

Count( {$<Status-={'WIP'}>} [OrderID] )

For WIP items it's trickier (because of accumulation). I think this is correct one:

rangesum(above( count( {$<Status={'WIP'}>} [OrderID]),0,rowno()))

Everything works (i checked with Excel manual calculations).

Though it seems very complicated way, maybe I got lost somewhere and there is more straight way?

Thanks!

Re: Accumulation in time series

Tue, 07 Jun 2016 17:55:33 GMT

Now I see that solution is not right...

For example, some orders maybe opened in May and closed in December. They will have OrderDate in May and completed in December.

But all months in between this order will be in WIP (June, July ... December).

Attaching the current application in QS.

I think I need to tweak with data model - it looks some additional columns may help. !