3 Replies Latest reply on Sep 28, 2015 9:36 AM by Ben Shirley

    calculation if date=null

    Armin Rauscher

      Hello,

      my problem is that i have a date where i have to do a calculation like the following:

       

      IF [date1] != NULL THEN

      NOW()-[date2]

      ELSE

      [date1]-[date2]

      END

       

      the date1 is filled with null or empty when the database entry is not finished, and i want to make a if question with this date1, but the tableau formula shows me the following error:

       

      can't compare datetime and NULL values.

       

      can someone tell me how i can compare dates?

       

      thanks

       

      Armin

        • 1. Re: calculation if date=null
          Alex Kerin

          You just need to use isnull([Date1])

           

          See attached

          1 of 1 people found this helpful
          • 2. Re: calculation if date=null
            Prashanth M V

            Hello Alex,

             

            I have a similar problem on date selections. Here is the scenarios.

             

            Problem1. I have start date and end date. I Should not allow user to select start date lesser than end date and similarly end date should not be greater than start date. What is the best possible solution for this.

             

            Problem 2: There is start date and end date, say from December 2014 to March 2015. There are few dates between these months where there is no data, say Jan 1 to Jan 4 - There is no data. If a user select this date, I need to display an error message - NO DATA FOUND.

             

            Could you please help me in finding a solution or a function for these 2 issues.

            • 3. Re: calculation if date=null
              Ben Shirley

              Hey,

               

              Can you expand on the Problem 1,

              Am i right in saying that you have Start Date as a filter option on your dash board as well as end date, but do not want them to cross?

               

              I think you would need to create a DATEDIFF parameter where the DIFF must always be >1.

              ill think on how to build that..

               

                   [EDIT
                       

                        I wrote this:

                             (DATEDIFF('day',[STARTDATE],[ENDDATE]))>1

                       

                        Then set the filter to "True" and then the 2 date filters to "ONLY SHOW RELEVANT VALUES"

                        When you adjust one, it updates the other to prevent overlap!

                        ]

               

               

               

              Problem 2 - Is a simple Calc if you want the reader to find "NO DATA FOUND"

               

              Create a Calculated Field and call it DATA

              Then write something like

               

              IF ISNULL([YOURDATA]) THEN "NO DATA FOUND"

              ELSE [YOURDATE]

              END

               

              Then use this field over the other data one, this should allow all date ranges to be returned then.

               

              Ben