2 Replies Latest reply on May 18, 2016 5:07 AM by 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 -

End -

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

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

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

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