1 Reply Latest reply: Jun 7, 2016 1:55 PM by vyacheslav guzanov RSS

    Accumulation in time series

    vyacheslav guzanov

      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:

       

      OrderIDOrderDateShipmentDateStatus

       

      very small sample table (Excel)

      https://www.dropbox.com/s/iff84s70obr7tkx/orders%20and%20shipments.xlsx?dl=0

       

      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!