Date calculation in analytics exspression

I have to do a bar graph the depend on shift hours, for that I have to calculate a specific OEE value, in the formula I have to determine if current time is in 1st shift (06:00 - 15:00), 2nd shift (15:00-23:00) or 3rd shift (23:00-06:00) and calculate how many minutes are between current time and start of shift.
For example, if current time is 12:02, do 12:02 - 06:00 = 362 minutes, take this data and multiple with other fields.
I did :
round(sum((@Good_package_current - @Good_package_previous ) - (@Bad_package_current - @Good_package_previous )) / (8.5 * 60 * sum(date_trunc(@Start Time -‘06:00:00’)), 2)
But it’s not ok, got error on it, and it’s just a part of the expression (have to add the if …)

Please help me, how can I develop such expression ?


I want just to clear something, I saw that the date_trunc doesn’t return a value of minutes, but I didn’t found any other function that does this conversion, so wrote the closest phrase, is there a function that returns minutes from begining of day from a desire hour ?


hello @ermingut, thanks for posting.

I would actually suggest solving this problem by adding a Variable in your App that is called Shift. upon starting or completing the App, you can store the Shift Name in that Variable depending on the If conditions on the @App Info.Current Date and Time. once you’ve done that, you can use that Variable to segment the Analysis as you’d like.

would this work as a solution for you?? let me know

I think that @Amit is trying to do all of this using MM only? I think that using an App here would also be a reasonable way to get more context on the data.

Hello all,
I have to do this function in the analytics only, not in the app, I have to make a bar graph of a calculated OEE value by a formula from my factory engineer.
For this formula I have the followin parameters :
A - Total Takts per minute (how many vakoom presses are in a minute)
B - Fix amount of takts per minute - constant of 8.5
C - Total minutes since begin of shift, for example, if the time now is 09:00 and the shift began at 06:00, so the number here will be 3 * 60 = 180
The formula is (A / (B * C)) * 100 that’s all, a very simple formula
The question in this discussion is how to calculate this time difference but in the analytics level, not in an application.

For the taks, I have a value of current non-initial field in the activity table that contain current number of takts since begin of machine history, so current record minus previous record for same field will return the value of current takt, this calculation I don’t know how to do either.

Please help me to resolve this issue.

Thank you,