Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!
SOLVED

sum values per shift

Highlighted
6 - Meteoroid

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!

Highlighted
8 - Asteroid

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.

 

Highlighted
15 - Aurora

Hi @Adolfo ,

 

My solution also uses the same approach as above.

 

Here is a working implementation of the solution using the sample data provided.

 

atcodedog05_0-1587233841483.png

 

atcodedog05_0-1587235977024.png

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

 

 

Highlighted
Alteryx Partner

Hi @Adolfo,

 

you could achieve this using a Formula and a Summarize-Tool.

 

grossal_0-1587234628562.png

 

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:

grossal_3-1587234780591.png

 

 

Output Summarize:

 

grossal_4-1587234804685.png

 

 

Workflow attached. Let me know if I got it right 😃

 

Best

Alex

Highlighted
6 - Meteoroid

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

Labels