3 Replies Latest reply on Jan 23, 2017 12:31 PM by Jamieson Christian

    What to do with Null in Calculated Field

    Jay Shields

      Greetings - I want to let user choose whether or not the Year of a patent application, App Date, should be used as a Column.  So I created a Yes/No String parameter called "Show Year of Application?"

      Then I created a calculation:

      IF [Show Year Of Application?]="Yes"

      THEN [App Date]

      END

       

      I then show show the parameter control and use the calculated field in the COLUMN shelf.

       

      This basically works.  However, when parameter is set to "No", the word "Null" appears as the column header.  How can I make it so that when parameter is set to "No", there is no column header.  When parameter is set to "Yes", I want the year to be shown as column header so I can't just deselect "show header".

       

      Many thanks.

       

      James

        • 1. Re: What to do with Null in Calculated Field
          Jamieson Christian

          Jay,

           

          The reason you get NULLs is because your calculation does not include an ELSE, so NULL is the fallback if no other conditions are met. Change your calculation to something like this (note that you'll have to normalize the date to a string to ensure the IF block returns the same data type):

           

          IF [Show Year of Application?] = "Yes"
          THEN STR([App Date])
          ELSE ""
          END
          • 2. Re: What to do with Null in Calculated Field
            Jay Shields

            Jamieson - when I do this, my calculated field in the COLUMN shelf turns red and says that "the calculation applies a date function to a non-date field".

             

            I get that I need to define a state for the "No" case.  Is there another way?

            • 3. Re: What to do with Null in Calculated Field
              Jamieson Christian

              Jay,

               

              Not that I can think of. The value will contain either a date (in which case NULL is the only option to represent a non-value), or a string. If you are doing a calculation in the COLUMN shelf, consider moving it into the calculated field I provided, so that you can convert the whole thing to a string without Tableau complaining.

               

              (Hint: attaching a Tableau packaged workbook would allow us to see the construction of your view and offer more detailed, and tested, feedback.)

              1 of 2 people found this helpful