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

    Calculations IFFF?

    Brandon Avants

      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?
          Matt Lutton

          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?
            Brandon Avants

            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?
              Matt Lutton

              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