topic Re: Counting overdue events to date in QlikView

Counting overdue events to date

arixooo123 — Tue, 20 Feb 2018 01:07:25 GMT

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

Re: Counting overdue events to date

dwforest — Tue, 20 Feb 2018 23:42:44 GMT

Small change to load script:

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

then

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

Re: Counting overdue events to date

arixooo123 — Tue, 20 Feb 2018 23:53:27 GMT

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 - .

Re: Counting overdue events to date

dwforest — Wed, 21 Feb 2018 00:22:52 GMT

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

Re: Counting overdue events to date

arixooo123 — Wed, 21 Feb 2018 00:41:59 GMT

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

Re: Counting overdue events to date

dwforest — Wed, 21 Feb 2018 16:47:36 GMT

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.