3 Replies Latest reply on Aug 26, 2013 12:55 PM by Matt Lutton

# Calculations IFFF?

I need to come back with a calculation that if a date is past due then to show me how many days past due.  If there is a null then to return nothing.  I've come up with the datediff but I'm stumped.  DATEDIFF('day',[Target Completion Date],TODAY()

• ###### 1. Re: Calculations IFFF?

You can leverage DATEDIFF within an IF statement, like:

IF

DATEDIFF('day',[Target Completion Date],TODAY()) > 5

then "Past Due"

else "Not Past Due"

end

You can filter out any Nulls--of course, you also have some Null handling calculations (IFNULL, ISNULL, ZN) that can be used to specify how to handle these values.

This is a very basic example, as I'm not very experienced with DATE functions, but you can go as far as you want with it from there.

• ###### 2. Re: Calculations IFFF?

Matthew,

Thanks but what I'm really looking for is for it to return the number of days past due and return nothing if there was a null value.

• ###### 3. Re: Calculations IFFF?

I understand.  What I provided will help you get there...  but returning the number of days will be a little more challenging.  Do you intend to include weekend days?  Holidays?  These things will affect your calculation.

There are many old threads on this forum on how to calculate # of days between dates.

If I am way off base, you may want to upload a packaged workbook with your work thus far, and show us precisely where the problem is.

Something like this should calculate the pure # of days, and return nothing otherwise:

IF

DATEDIFF('day',[Date],TODAY()) > 30  (assumes 30 days previous to today is the cutoff point)

then DATEDIFF('day',[Date],TODAY())

else NULL

end

Someone with more experience may be able to provide better assistance; I'm just trying my hand at helping wherever possible on the forum in order to learn more myself (only been using Tableau for a couple of months now myself)

For only weekdays, this thread (or others like it) may provide some guidance: http://community.tableau.com/thread/107647?start=0&tstart=0