Hello,
I've been trying many hours and ways but I'm not able to do something (new user here…)
I have a table with two columns:
-date-hour (data type: DateTime)
-values (data type: Double)
date-hour values
2020-04-01 06:00:14 2317.7
2020-04-01 07:00:14 2542.9
2020-04-01 08:00:16 2173.5
2020-04-01 09:00:14 2202.3
2020-04-01 10:00:14 2355.4
2020-04-01 11:00:15 2386.6
2020-04-01 12:00:16 2233.7
2020-04-01 13:00:14 2208.6
2020-04-01 14:00:14 1938.2
2020-04-01 15:00:14 1906.2
2020-04-01 16:00:15 2018.3
2020-04-01 17:00:16 1774
2020-04-01 18:00:15 2016.9
2020-04-01 19:00:16 2082.5
2020-04-01 20:00:16 2099.9
2020-04-01 21:00:15 2117.4
2020-04-01 22:00:14 1971.1
2020-04-01 23:00:15 2030.1
2020-04-02 00:00:16 2026.7
2020-04-02 01:00:16 1939.8
2020-04-02 02:00:16 2060.7
2020-04-02 03:00:16 2093.9
2020-04-02 04:00:15 1936.6
2020-04-02 05:00:15 1879
etc (many days)
I would like to sum the values for every day grouped by 3 working shifts:
- Morning shift: >= 06:00:00 and < 14:00:00
- Afternoon shift: >= 14:00:00 and < 22:00:00
- Night shift: >= 22:00:00 and < 06:00:00
Therefore, I should have something as follows:
date-shift values
2020-04-01 MS 18420.7
2020-04-01 AS 15953.4
2020-04-01 NS 15937.9
2020-04-02 MS 15759.2
etc
etc
Thank you very much!
Solved! Go to Solution.
Hi @Adolfo,
I would suggest using a formula tool to create an attribute that categorizes the datetime field into the YYYY-MM-DD Shift format. Then you could use the summarize tool to group by that attribute and sum the Values column.
Here's a mockup of what the expression in the formula tool might look like. In my example, my datetime field was the field [Time].
IF DateTimeHour([Time]) >= 6 AND DateTimeHour([Time]) < 14 THEN ToString(ToDate([Time])) + ' MS'
ELSEIF DateTimeHour([Time]) >= 14 AND DateTimeHour([Time]) < 22 THEN ToString(ToDate([Time])) + ' AN'
ELSEIF DateTimeHour([Time]) >= 22 THEN ToString(ToDate([Time])) + ' NS'
ELSEIF DateTimeHour([Time]) < 6 THEN ToString(ToDate(DateTimeAdd([Time],-1,"days"))) + ' NS'
ElSE '' ENDIF
You may want to tweak the last ELSEIF statement depending on how you want to handle the first six hours of the day. I wasn't sure if that should show the actual day value or the previous day's value since that shift technically started at 10 PM the day before.
Please let me know if you have any questions.
Hi @Adolfo ,
My solution also uses the same approach as above.
Here is a working implementation of the solution using the sample data provided.
Where output also matches your sample output.
I hope this helps your purpose. If there is still anything specific you are looking for please reply back.
Thank you for sharing this scenario. It was a great exposure.
Happy Analyzing : )
Hi @Adolfo,
you could achieve this using a Formula and a Summarize-Tool.
Formula:
DateTimeFormat([date-hour],'%y-%m-%d') + ' ' +
IF DateTimeHour([date-hour]) >= 6 AND DateTimeHour([date-hour]) < 14
THEN 'MS'
ELSEIF DateTimeHour([date-hour]) >= 14 AND DateTimeHour([date-hour]) <= 22
THEN 'AS'
ELSE 'NS'
ENDIF
Output Formula:
Output Summarize:
Workflow attached. Let me know if I got it right 😃
Best
Alex
Thank you very much! You were right about the shifts and I made a mistake. I've found the following solution:
IF DateTimeHour([Fecha-Hora-Turno]) >= 6 AND DateTimeHour([Fecha-Hora-Turno]) < 14 THEN ToString(ToDate([Fecha-Hora-Turno])) + ' TM'
ELSEIF DateTimeHour([Fecha-Hora-Turno]) >= 14 AND DateTimeHour([Fecha-Hora-Turno]) < 22 THEN ToString(ToDate([Fecha-Hora-Turno])) + ' TT'
ELSEIF DateTimeHour([Fecha-Hora-Turno]) >= 22 THEN
ToString(ToDate(DateTimeAdd([Fecha-Hora-Turno],+1,"days"))) + ' TN'
ELSEIF DateTimeHour([Fecha-Hora-Turno]) < 6 THEN
ToString(ToDate([Fecha-Hora-Turno])) + ' TN'
ElSE '' ENDIF