topic Re: KPI Date comparisons challenges Sense(3.2.4) in App Development

KPI Date comparisons challenges Sense(3.2.4)

deerhunter — Mon, 12 Jun 2017 01:22:35 GMT

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 — Mon, 12 Jun 2017 03:31:15 GMT

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 — Tue, 13 Jun 2017 13:56:51 GMT

Can you add a barchart that shows counts by month?

Thanks

deerhunter — Tue, 13 Jun 2017 14:21:54 GMT

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

lironbaram — Tue, 13 Jun 2017 19:10:09 GMT

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 — Thu, 15 Jun 2017 16:36:53 GMT

Yes please

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

Thanks