How to convert days ex Monday Tuesday etc in string data type to day type in Tableau

Question

I have a column called "Day of Week" that has information on the days of the week, such as "Monday," "Tuesday," "Wednesday," and so on. It always appears as string data type when I import it into Tableau. In order to conduct time series modelling, I need to change it to a day data type under Datetype. However, when I used the DATEPARSE method to convert a string to date format, it failed every time.

DATEPARSE('MMM',[Day of Week]) DATEPARSE('EE,EEEE','Day of Week'])

Could someone please assist me? Is there anything I'm missing?

Answer 1

To create a date from constituent integers, use MAKEDATE.

With: Y,M,D as 2020,8,25

the date is created as MAKEDATE([Y],[M],[D])

The string day is not sufficient for the date calculation because there are several e,g, Mondays each month. You need the day of the month.

That said, if you do want to convert the string day to an integer, it can be done using CASE, as follows, where day is the string day:

CASE [Day] WHEN 'Sunday' THEN INT(1) WHEN 'Monday' THEN INT(2) WHEN 'Tuesday' THEN INT(3) WHEN 'Wednesday' THEN INT(4) WHEN 'Thursday' THEN INT(5) WHEN 'Friday' THEN INT(6) WHEN 'Saturday' THEN INT(7) END

Elevate your data visualization skills with our Tableau Course and unleash the power of insightful analytics.