Sorting data based on the day of the week

[Deano478]

Hi Alteryx Crew,

I'm pretty new to Aletryx and have what might be a simple problem to solve but I'm trying to order this data based on the Day_of_Week from "Monday" to "Sunday". to get the day of the week for each date I used the formula DateTimeformat([Submission_Date], "%A").  As mentioned it might be simple problem to solve but I'm just drawing a blank.

All help is much appreciated guys and gals. 

Activity_ID	Submission_Date	Responders_Email	Repeat_For_The_Week	Day_of_Week
221	10/09/2021 08:17	una.Murphy@test.com	Repeat my activity index for the remainder of the week	Friday
320	13/09/2021 16:30	yiu.Rao@test.com	Repeat my activity index for the remainder of the week	Monday
314	11/09/2021 16:34	Mark.Alonso@test.com	Repeat my activity index for the remainder of the week	Saturday
1861	03/10/2021 22:06	Kevin.Martins@test.com	Repeat my activity index for the remainder of the week	Sunday
112	09/09/2021 16:30	Maria.Union@test.com	Repeat my activity index for the remainder of the week	Thursday
441	14/09/2021 16:30	Tony.Barker@test.com	Repeat my activity index for the remainder of the week	Tuesday
11	08/09/2021 16:37	Joe.Bloggs@test.com	Repeat my activity index for the remainder of the week	Wednesday

[gautiergodard · Accepted solution]

@Deano478one way to do this would be to create a formula to assign a number to each day of the week and then sort based on that newly created field.

example:

if dayofweek = Monday then 1

elseif dayofweek = Tuesday then 2

elseif dayofweek = Wednesday then 3

etc…

hope this helps !! 

[Felipe_Ribeir0 · Accepted solution]

Hi @Deano478 

You can use the DateTimeFormat([Submission_Date], '%u') formula to transform the date into a week day number between 1 and 7 and order by it.

%u	Day of week as a decimal, 1 to 7, with Monday as 1

[Sarreddy · Accepted solution]

@Deano478 

[Deano478]

@Sarreddy, @Felipe_Ribeir0, @gautiergodard. Thank you all so much for you various approaches I tired all of them and they all work very well in different ways this has given me a strong idea in my head now going forward. Its the simplest things that make a big difference going forward