KPI Date comparisons challenges Sense(3.2.4)

[deerhunter]

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

[lironbaram]

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]

Can you add a barchart that shows counts by month?

Thanks

[deerhunter]

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

[lironbaram]

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]

Yes please

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

Thanks