5 Replies Latest reply on Aug 30, 2013 11:56 AM by Patricia Santillan

# Create a variable where mon - Fri = Weekday, Sat and Sun separate

I work for a bus company and the same route will have a different schedule for M-F, one for Sat and one for Sun.  In excel I can use a formula to first tell me the day number (1-7) and then I can use an if statement to say if the day of this date is a 7(Saturday), show me "SAT, if a 1(Sunday), show me "SUN" else show me "Weekday".  How do I do this in Tableau?  I need to group ridership totals by this variable and I can't figure out how it can work.  Thanks in advance!

• ###### 1. Re: Create a variable where mon - Fri = Weekday, Sat and Sun separate

Hi Patricia,

Creating a calculated field similar to the following should help:

if datepart('weekday',[DATE])=1 then 'Sunday'

elseif datepart('weekday',[DATE])=7 then 'Saturday'

else 'Weekday' end

-Tracy

• ###### 2. Re: Create a variable where mon - Fri = Weekday, Sat and Sun separate

Thank you so much that was perfect!!

Have an extra issue if you have time...

We treat holiday service as Sunday service since there are less riders.  Assuming I create variable which says whether a day is a holiday (t/f) how can I adjust this formula to say if this date is NOT a holiday (False)  then the formula above applies, if this date IS a holiday (True) then mark as a Sunday.  I haven't created the variable yet but lets assums the variable is just called "Holiday".  Thanks again for your consideration.

• ###### 3. Re: Create a variable where mon - Fri = Weekday, Sat and Sun separate

If you are gathering right answers I can ask this as a separate question if you want.

• ###### 4. Re: Create a variable where mon - Fri = Weekday, Sat and Sun separate

So it sounds like there will be another dimension that determines the holiday?

A calculated field like the following should work:

if [Holiday]=true then 'Sunday'

elseif datepart('weekday',[DATE])=1 then 'Sunday'

elseif datepart('weekday',[DATE])=7 then 'Saturday'

else 'Weekday' end

-Tracy

1 of 1 people found this helpful
• ###### 5. Re: Create a variable where mon - Fri = Weekday, Sat and Sun separate

Thank you so much for your help!