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

    Date Equalizing Formula Issue

    Ben Benhamou

      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
          Jim Dehner

          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
            Ben Benhamou

            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... !

            • 3. Re: Date Equalizing Formula Issue
              Jim Dehner

              Hi Ben

              this is what I get

               

               

              Jim

              • 4. Re: Date Equalizing Formula Issue
                Alyn Still

                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])