2 Replies Latest reply on May 18, 2016 5:07 AM by Graeme Ord

    DATEADD Issue/Question

    Graeme Ord

      Hi - hoping the Tableau community can help me with a piece of logic in a calculation that I think should work but does not appear to.

       

      I am trying to determine if a person started and left a job within 90 days of being hired and create a flag that I can then count, my logic is as below and this does not work:

       

      IF NOT ISNULL([HiredDate]) THEN

          IF [HiredDated] >= [BetweenDateStart] AND [HiredDate] <= [BetweenDateEnd] THEN

              IF ([Date Of Leaving] <= DATEADD('day',90,[HiredDate]) THEN

                  1

              END

          END

      END

       

      However, if I use [ReportingMonthDate] - or indeed any date that has not had a DATEADD function applied - such as, then it returns the values I am expecting:

       

      IF NOT ISNULL([HiredDate]) THEN

          IF [HiredDated] >= [BetweenDateStart] AND [HiredDate] <= [BetweenDateEnd] THEN

              IF ([Date Of Leaving] <= [ReportingMonthDate] THEN

                  1

              END

          END

      END

      Incidentally, [BetweenDateStart] and [BetweenDateEnd] are other calculations:

       

      Start -

      datetrunc('month',dateadd('month',-4,date ([ReportingMonthDate])))

       

      End -

      DATETRUNC('month',DATEADD('month',1,[BetweenDateStart]))-1

       

      Am I missing something - can anyone shed any light as to what I may be doing wrong?

       

      Many Thanks,

       

      Graeme

        • 1. Re: DATEADD Issue/Question
          Andrew Watson

          In these cases it's best to lay all of the relevant calculations out in a table and see where the unexpected results appear. In this case create a table with:

           

          Hired Date

          BetweenDateStart

          BetweenDateEnd

          DATEADD('day',90,[HiredDate])

          ReportingMonthDate

          [HiredDated] >= [BetweenDateStart] AND [HiredDate] <= [BetweenDateEnd] (see if TRUE or FALSE is returned)

          [Date Of Leaving] <= DATEADD('day',90,[HiredDate]) (see if TRUE or FALSE is returned)

          ([Date Of Leaving] <= [ReportingMonthDate] (see if TRUE or FALSE is returned)

           

          Also put your 2 calculated fields in the table and you should be able to see where the calculation is returning results you don't expect to see.

          1 of 1 people found this helpful
          • 2. Re: DATEADD Issue/Question
            Graeme Ord

            Thanks Andrew - drilled down into the data and found the issue and it was with the data set rather than the calculation.

             

            Added a couple of additional clauses (i.e. checking if the leave date was null) as well just to ensure the calculation runs only when I have a valid start and leave date.

             

            Many Thanks,

             

            Graeme