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.
In looking at the help pages, it appears as we have add, subtract, multiply, and divide along with parentheses to calculate (This is in Phocas itself, not anything with the data before it gets into Phocas). I don’t think if then statements are available.
I am thinking you could do this as a widget if using on a dashboard. If you used the variance of the promised and actual delivery fields, you could then set up the variance percentages to show green, red, etc.
Thinking of how to pull this info prior to Phocas, I would suggest a tickler table in SQL doing the heavy lifting for you, then just pull the true/false result over.
I’m assuming your CASE statement is a custom column (deliverytime?) in the data. Since you can’t use that custom column, just nest the expression:
CASE WHEN
(case when isdate([departuretime]) = 0 or isdate([dispatchtime]) = 0 then 0 else datediff (minute, [invoicetime], [arrivaltime])
end - prioritycode) < (prioritycode*.1)
THEN TRUE
ELSE FALSE
END
PriorityCode is not in brackets, either. If that is a custom column you will nest that as well.
The only other thing I noticed is the isdate reference. be careful with this. I’m not sure about your instance, but in ours dates are stored as strings so we have to format first CAST([Reqd_Date] as date). Without this isdate will always return false.
