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: 
deerhunter
Creator
Creator

KPI Date comparisons challenges Sense(3.2.4)

The KPI needs to count all tickets that have not been completed on-time from the ticket start date ( plus 60 days)

     where its greater then the actual completion date .

The first snag is that some of the start dates can be null, also the completion date might be null if it wasn't completed yet.

So the rule is : Count (startdate +60 > completiondate)

the two fields are datetime formatted

Question is how do i plan for this?

Sample dates provided:

     Startdate                 = 05/09/2017 7:43:50 AM

     Completiondate      = 07/03/2017 9:34:45 PM

I included some sample dates.

The following attempt is not returning anything

Count({< [date([startdate]={">=$(=date([startdate] +60, 'MM/DD/YYYY')) >$(=DATE(completiondate, 'MM/DD/YYYY')) "} >} [Tickets]))

What about the nulls, how do i deal with that?

What is the secret , and an example would be great.

Thanks community

5 Replies
lironbaram
Partner - Master III
Partner - Master III

hi

attach is an example

some references

  • for completion date i inserted now() for null values
  • you can use the same logic to set a minimum value to all null values in startdate
  • in the front end it's an easy expression , you can't use set analysis because it's calculated at app level and not row level
deerhunter
Creator
Creator
Author

Can you add a barchart that shows counts by month?

Thanks

deerhunter
Creator
Creator
Author

Can you add a barchart that shows counts by month of the late results?

lironbaram
Partner - Master III
Partner - Master III

hi

do you want the chart to check for each instance in the end of each month

it was open if it's late or not ?

deerhunter
Creator
Creator
Author

Yes please

And if you could show also the average days it took to complete.

Thanks