Counting overdue events to date

[arixooo123]

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

[dwforest]

Small change to load script:

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

then

Count ({$<OpenLate={1}>}ID)

[arixooo123]

Hi David,

I want to see the trend of that KPI over time, a currently overdue event shouldn't be counted in the month before.

I am thinking about something like this:

Calculating DueDate in Load script by adding 30 working days to the OpenDate. Then in expression tab of the line chart:

Count( if (DueDate < today() and status={'open'} ) ID)

but it doesn't work, it actually returns either 0 or - .

[dwforest]

Count ({$<Status={'open'},DueDate={"<=$(=Today())"}>}ID)

[arixooo123]

Thanks David

The script works and the syntax is definitely correct.

Just one question, any idea how today() function works in a line chart trend over months?

In below picture, what is the value of today in prior months?

when I want to calculate the KPI in September, is it smart enough to change the value of today() to '20XX-09-01' or '20XX-09-30' ?

[dwforest · Accepted solution]

Today() is today, not last day in the selected range. To get that, just use max(DateField); this would probably mean you need a master calendar to easily divide things into months, quarters, weeks, etc. and cover gaps in your data, e.g. if there were no open tickets for 1/31.