6 Replies Latest reply on Jun 27, 2016 1:24 PM by Saurabh Siroya

# Date Question

Last year, Jon Munger with Tableau Support was able to help with with a formula to assign a value to a date that would later be used to assign a name to that value (0=A, 1=B, 2=C). This calculated field worked fine for 2015, but it appears that the Shift Name is off for 2016 and I have to adjust the offset. Can anyone explain why this happened? Is there a better way to accomplish this without having to check the offset at the beginning of each year since this will cause issues with YoY reporting?

Calculated Field - Shift Value

IF DATEPART('hour',[Unit Enroute Date + Time])>=7 THEN

((DATEPART('dayofyear',[Unit Enroute Date + Time]))+1)%3

ELSE

(DATEPART('dayofyear',[Unit Enroute Date + Time]))%3

END

Calculated Field - Shift Name

IF [Shift Value] = 1 THEN "B Shift"

ELSEIF [Shift Value] = 2 THEN "C Shift"

ELSE "A Shift"

END

Thank you!

• ###### 1. Re: Date Question

The calculation is dependent upon the day of the year divided by 3, so each year will shift because 365 is not divisible by 3. Instead you have to pick a fixed date to do you calculation against.

i.e.

IF DATEPART('hour',[Unit Enroute Date + Time])>=7 THEN

((DATEDIFF('day'',[base_Date],[Unit Enroute Date + Time]))+1)%3

ELSE

(DATEDIFF('day'',[base_Date],[Unit Enroute Date + Time]))%3

END

if you pick the base date correctly, it should work fine YoY

• ###### 2. Re: Date Question

Thank you, Jason!

One question... If I select the base date as 1/1/2015, but filter my data to only include values between 12/1/2015 - 12/31/2015, will that cause an issue? The only other fix that I can think of is to save a sheet for each year with the Shift Value adjusted for each year.

• ###### 3. Re: Date Question

I would hard code the "Base Date" so any date filter (or other filters) will not affect it.

As you can see below, I hard coded the date by wrapping it in octothorpes (it's not often I get to use that word ).

I did not test this to see what happens if you have a [Unit Enroute Date + Time] date BEFORE Jan 1, 2015 ... so pick the hard coded date carefully.

IF DATEPART('hour',[Unit Enroute Date + Time])>=7 THEN

((DATEDIFF('day',#2015-Jan-01#,[Unit Enroute Date + Time]))+1)%3

ELSE

(DATEDIFF('day',#2015-Jan-01#,[Unit Enroute Date + Time]))%3

END

1 of 1 people found this helpful
• ###### 4. Re: Date Question

I created a duplicate Shift Value calculated field and included your hard coded content from above. Our database contains information from 2004, so I used 1/1/2014 as the base date and set the shift values accordingly. It appears that your suggestion works, even with a 2015 and 2016 filter applied.

Thank you so much for taking the time to look at my question!

• ###### 5. Re: Date Question

Hello,

I have a business requirement where I want to use the relative filter but the current date should be a user input and based on that I should be able to select Weekly, Monthly and Quarterly view. Or is there a way to create a date as an input for the user and based on that I will have a list from which I can select weekly, Monthly or a Quarterly view.

Regards:

Saurabh Siroya

• ###### 6. Re: Date Question

Hello,

I have a business requirement where I want to use the relative filter but the current date should be a user input and based on that I should be able to select Weekly, Monthly and Quarterly view. Or is there a way to create a date as an input for the user and based on that I will have a list from which I can select weekly, Monthly or a Quarterly view.