sum values per shift

[Adolfo]

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!

[DQAUDIT]

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.

[atcodedog05]

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 : )

[grossal]

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

[Adolfo · Accepted solution]

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