Help with expression

[#1]

Need help with writing an expression!

I'm trying to create a dashboard to show KPI targets, and one of them is to messure actual completion dates against SLA completion date for work orders.

I need to count all the work orders where the Date Finished is less than the Date Escalated for Completion

Ultimately this would be to compare to a count of all completed work orders to messure against the 70% KPI target.

Couple of things I need to cater for within the expression:  A work order could have multiple engineer visits, the Date Finished field is from the time record of each engineer visit, so it is the latest engineer visit we are using in the KPI against the SLA completion date for the work order.  So I'm using "max([Date Finished]) to get this - if this is the correct or best way to go about this!!!

So far i've tried the following expression (in a text object), which I know doesn't work, but its a starter to try show what I'm doing:

=count(DISTINCT{<max([Date Finished])<[Date of Completion Escalation]>},[Work Request Code])

Any ideas how to get the right calculation/expression???

Thanks

Dan

[sunny_talwar]

Try this, I have not tested it, but I think it should work:

=Count(DISTINCT{<[Date Finisted] = {'=Max([Date Finished])<[Date of Completion Escalation]'}>} [Work Request Code])

Best,

Sunny

[sunny_talwar]

Actually this may not work. Try this:

=Count(DISTINCT If(Max([Date Finished]) < [Date of Completion Escalation], [Work Request Code]))

Best,

Sunny

[#4]

Tried, still gives me "Error in expression" and no value in text object!!

[Peter_Cammaert · Accepted solution]

=count(DISTINCT if ([Date of Completion Escalation] > aggr({1} max([Date Finished]), [Work Request Code]), [Work Request Code]))

[#6]

Seems to work, thanks!