5 Replies Latest reply: Feb 21, 2018 11:47 AM by David Forest RSS

    Counting overdue events to date

    ar eiuiew

      This is the simplified version of the data model:

       

      ID             OpenDate            DueDate                 Status                 Closed Date

      123          2018-01-01         2018-02-01               Open                      -

      124          2018-01-02         2018-03-01               Closed               2018-01-20

      125          2018-01-03         2018-02-03               Open                      -

      126          2018-01-04         2018-02-05               Closed                2018-01-28

      127          2018-01-05         2018-02-06               Open                      -


      We need a Trend of the KPI which is % of closed events less than 30 days

      An overdue event should affect our KPI since it was overdued untill it's closed.


      For instance, eventID of 123 is overdue now.  assuming that this event will be closed 6 months later. it should start affecting our KPI since 2018-03-01 when it's overdued untill it's closed.

      KPI:

      Count( Closed InTime Events) /  (Count( Closed InTime Events) + Count(Currently Overdue events) )


      My problem is how to calculate Currently overdue events for the prior months!

      I know how to label them in Load script like this:


      if( networkdays([OpenDate],today())>30 ,if(status={'open'},'OpenLate'))


      Just don't know how to count them in set analysis?

       

      Count ({OpenLate}ID)


      I really hope you can understand what I said, it's a little bit confusing