topic Re: Help with expression in QlikView

Help with expression

Thu, 02 Apr 2015 10:08:11 GMT

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

Re: Help with expression

sunny_talwar — Thu, 02 Apr 2015 10:16:36 GMT

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

Re: Help with expression

sunny_talwar — Thu, 02 Apr 2015 10:18:36 GMT

Actually this may not work. Try this:

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

Best,

Sunny

Re: Help with expression

Thu, 02 Apr 2015 10:23:12 GMT

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

Re: Help with expression

Peter_Cammaert — Thu, 02 Apr 2015 10:26:08 GMT

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

Re: Help with expression

Thu, 02 Apr 2015 11:56:17 GMT

Seems to work, thanks!