4 Replies Latest reply on Jan 30, 2019 11:50 AM by Alyn Still

# Date Equalizing Formula Issue

Hi

I am trying to replace the year in my date field by the current year to compare each years on a Month + Day (example July 1st to Jan 23rd for each year) with my fiscal year starting July 1st

I've come across this date equalizer technique online which works perfectly, except for one specific date, Feb 29th 2016, which does not exist in 2018-2019

So I'm trying to specifically change my 29/02/2016 to 28/02/2016 and then 28/02/2019

Original formula that worked:

IF MONTH([Operation Timestamp])>=7

THEN(STR(MONTH([Operation Timestamp]))+"/"+STR(DAY([Operation Timestamp]))+"/2018")

ELSE (STR(MONTH([Operation Timestamp]))+"/"+STR(DAY([Operation Timestamp]))+"/2019")

END ))

This is my new formula that I can't seem to write properly:

IF [Operation Timestamp] = '29/02/2016' THEN(STR(MONTH([Operation Timestamp]))+"/"+STR(DAY([Operation Timestamp]-1))+"/2019"

ELSEIF MONTH([Operation Timestamp]) >=7 THEN(STR(MONTH([Operation Timestamp]))+"/"+STR(DAY([Operation Timestamp]))+"/2018"

ELSEIF MONTH([Operation Timestamp]) <7 THEN(STR(MONTH([Operation Timestamp]))+"/"+STR(DAY([Operation Timestamp]))+"/2019"

END

What am i missing? Is this the best technique for what i am trying to do? Thank you for your help!

• ###### 1. Re: Date Equalizing Formula Issue

Hi Ben

It would help knowing what error it is throwing and where it is in calc - that said it looks like you are comparing {Operation Timestamp} a date to a literal Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Date Equalizing Formula Issue

Hi Jim,

Sorry I didn't precise this, my field that's named Operation Timestamp is my Date field  (just like a typical Order Date field)

The calculation is showing me as valid but it is not converting the 29/02/2016 into 28/02/2019 (it's still showing as NULL in my viz, because there isn't any 29/02/2019)

Thanks for your help, and also I am new to Tableau... !

• ###### 4. Re: Date Equalizing Formula Issue

You can probably do this with straightforward date math - instead of having to generate & combine strings.

This method also has the benefit of working across all dates - so you don't have to worry that if the report is still in use next year you'll need to go in to update "2019" to "2020"

dateadd('year'

, datediff('year'

, [Appointment Date]

, today()

)

,[Appointment Date]

)

This works out the number of years between the date and today. Then subtracts that many years from the date field (in my case [Appointment Date])