Hi all! I am trying to quantify a metric to determine on-time delivery service. In English, I would query as to whether the difference between the actual delivery time and the priority window delivery time is within 10% of the priority delivery time. Said numerically, whether the difference between 31 minutes and 30 minutes is greater than or less than 10% of 30 minutes (or 3 minutes).
Within sync I can create my own column to express this as an expression and writing an expression seems to be a bit SQL-esque but not all the way. Adding to the mix, I have to also calculate delivery time using the is date function and this is where I start to get stuck by subtracting invoice date from delivery date while excluding items that have yet to have a delivery date.
As an expression I would calculate delivery time as follows:
case when isdate([departuretime]) = 0 or isdate([dispatchtime]) = 0 then 0 else datediff (minute, [invoicetime], [arrivaltime]) end
This gets me a delivery time (thanks Cody!) but now I have to re-use this expression as part of my overall boolean request to return a TRUE FALSE based on the above query… How do I do it.
If I lived in excel it would be:
=if((deliverytime - prioritycode)<(priority code *.1), TRUE, FALSE.
=if((31-30)<(30*.1) = TRUE <-- meaning on-time delivery.
On another related note, there has to be some documentation lying around on how to write expressions…