6 Replies Latest reply on Feb 6, 2012 10:05 AM by Jonathan Drummey

    nz function equivalent in Tableau

    thierryjakircevic

      Hi,

       

      I'd like to calculate the minimum value between 2 different fields. Since these fields can be null. In Access, I am using the following formula: min(nz(Field1),nz(Field2)) so that null values are handled properly (ie: not counted as 0)

      Is there an equivalent in Tableau?

       

      I have tried the “ZN” function but it converts my null value as 0 which is incorrect (the null value should simply be omitted).

       

      Thank you in advance for pointing me in the right direction!

       

      Thierry

        • 1. Re: nz function equivalent in Tableau
          Shawn Wallwork

          This might work: MIN(IFNULL([Field1], NULL), IFNULL([Field2] ,NULL ))

           

          I tested its validity, but I didn't test its result. Let me know if it does what you're looking for.

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: nz function equivalent in Tableau
            Jonathan Drummey

            Shawn - I think I was working on this the same time as you, here's what I found out:

             

            From trying different values in the MIN() expression, it appears that Null evaluates to "lowest value" as compared to any other number, positive or negative. In other words, a MIN() with a Null in in it will always return Null. So, here's an alternative:

             

            MIN(IF NOT ISNULL([Field1]) THEN [Field1] ELSE 1e30 END, IF NOT ISNULL([Field2]) THEN [Field2] ELSE 1e30 END)

             

            The 1e30 is there because we don't want the expression to evaluate to something that would be an answer, so set that as high as you need to.

            • 3. Re: nz function equivalent in Tableau
              Shawn Wallwork

              Jonathan, thanks for the correction and info. (I was being lazy and not doing the testing before posting.)

               

              --Shawn

              • 4. Re: nz function equivalent in Tableau
                thierryjakircevic

                Thank you Shawn and Jonathan for your guidance.

                 

                I need to correct my original post: in Access the function: min(nz(Field1),nz(Field2)) would also return null as the minimum value.

                I was using the nz function to allow the SUM of multiple fields that would contain null value but it won't work with MIN.

                 

                Anyway, thank you very much for the recommendation!

                 

                Thierry

                • 5. Re: nz function equivalent in Tableau
                  Alex Blakemore

                  it appears that Null evaluates to "lowest value" as compared to any other number, positive or negative.

                  Not exactly. Null means missing value, or no information, or maybe even not applicable. A null value doesn't have a defined order when compared to a non-null value.

                   

                  Remember there are multiple types of calculations: row level (normal) calcs, aggregate calcs, table calks.

                   

                  Normal calculated fields return a value for each row in the data. You can think of them as adding an extra column in the data source defined by a formula that can only refer to other cells in the current row. Aggregate calculated fields return one value for a block of rows, which is why they need to use aggregation functions to reduce a set of values to a single result.

                   

                  From my experience and testing, null values are ignored by aggregation functions. So Sum() or Avg(), for example only consider the non-null values when aggregating over a set of rows.

                   

                  For most row level functions, if one of the arguments is null, the function returns null. So Sin(null) is null. There are exceptions -- zn(), isnull() and iif() can return non-null values, even if one of the arguments is null. For example, iif(1<2, 3, null) returns 3 even though the last argument is null.

                   

                  If you've used SQL for a while, these rules should look familiar.

                   

                  Min() and Max() are special in that they can act as either row level functions or aggregate functions, depending on the number of arguments. min([my_field]) is an aggregate calculate field that will ignore null values, and will return null if all the values are null. min([my_field], 3) is a normal row level calculated field that will return null for those rows where [my_field] is null, and either [my_field] or 3 for the other rows.

                   

                  BTW, if you want to return the min of two fields, but don't want to return null when only one of the fields is null, here's an expression that does not depend on an arbitrary large number. You are in effect taking responsibility to define the semantics in the case mixing nulls and non-null values.

                  if isnull([Field1)] then

                    [Field2]

                  elseif isnull([Field2]) then

                    [Field1]

                  else

                    min([Field1], [Field2])

                  end

                   

                  I'll let someone else discuss table calcs

                  1 of 1 people found this helpful
                  • 6. Re: nz function equivalent in Tableau
                    Jonathan Drummey

                    Alex, thanks for the clarification and alternative calculation!