Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arixooo123
Creator III
Creator III

Counting overdue events to date

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

1 Solution

Accepted Solutions
dwforest
Specialist II
Specialist II

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.

View solution in original post

5 Replies
dwforest
Specialist II
Specialist II

Small change to load script:

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

then

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

arixooo123
Creator III
Creator III
Author

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
Specialist II
Specialist II

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

arixooo123
Creator III
Creator III
Author

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' ?

Capture.PNG

dwforest
Specialist II
Specialist II

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.