1 Reply Latest reply on Dec 19, 2013 10:18 AM by Kevin Sullivan

    If Statement remove Null

    Kevin Sullivan

      I have a parameter selection box [Alternative Hierarchies] which you can choose 'Shared Services', 'IT Org', or 'WIA Orgtl'.  When you do this the below IF statement is applied to a quick filter.  When Shared Services or IT Org are chosen the Null value exists in the quick filter which makes sense since there are null values in the data for those 2 and there is no null values when WIA Orgtl is chosen because there are no nulls for WIA Orgtl. 

       

      I want to add to this formula so when 'Shared Services' or 'IT Org' are selected the "Null" value in the quick filter does not appear and when WIA Orgtl is selected all records appear.  I have tried doing a duplicate field and filtering Null but the 3 selections come from different fields so when null is off it make the Shared Services and IT Org correct but it also limits the WIA Orgtl results.  Any help would be greatly appreciated.

       

      If [Alternative Hierarchies]='Shared Services'
      Then  [SS Reporting Group]
      elseif [Alternative Hierarchies]='IT Org'
      Then [IT Org Reporting Group]
      elseif [Alternative Hierarchies]='WIA Orgtl'
      then[Business Group Name]end

        • 1. Re: If Statement remove Null
          Kevin Sullivan

          Got the right answer from Jonathan Drummey!  Thank you Jonathan.


          Instead of loading up the IF calc, create an additional calc that use the parameter to ID when Shared Services or IT Org is selected, like this one:

           

          CASE [Alternative Hierarchies]

                          WHEN “Shared Services” THEN IIF(NOT ISNULL([Shared Services]),1,Null)

                          WHEN “IT Org” THEN IIF(NOT ISNULL([IT Org]),1,Null)

                          ELSE 1

          END

           

          Make this discrete and filter for 1. I’m using 1 and Null instead of a Boolean True/False because Tableau won’t let us return Booleans from CASE or IF/THEN statements.

           

          Jonathan