4 Replies Latest reply: Sep 21, 2016 10:23 AM by Ruben Marin RSS

    isNull different behaviour

    PAOLO MAPELLI

      Good Morning,

      I've a loaded a simple dataset with two columns (start date and end date).

       

      Now I've created a straight table with two columns: for first one I chosen a dimension type column and put "isnull([Start Date])" in its definition, for second one same thing but this time I've chosen a measure type column.

       

      Could you kindly explain me why isNull function has a different behaviour between the two different column types ?

       

      I'm using QS Desktop v.3.0

       

      Thanks in advance.

       

      Paolo.

      Clipboard01.jpg

        • Re: isNull different behaviour
          Jayaseelan K

          Hi paolo,

           

          Its Depends up on your requirement.

           

          Most of the case isnull function is use with if conditions.

           

          1.While using isnull in dimension, it will check the null rows in the particular column and then replacing the null row.

          2.While using isnull in measurement, it will check the rows and do's some calculations.

           

          https://help.qlik.com/en-US/sense/3.0/Subsystems/Hub/Content/Scripting/NULLFunctions/IsNull.htm

           

          refer above link.

           

          Hope this will helps you.

           

          Thanks,

          • Re: isNull different behaviour
            Toni Kautto

            The simple answer is that IsNull() returns different results because you are evaluating different value in the two columns. I can not really understand the purpose of a test like this. What are you trying to accomplish?

             

            The calculated dimension generates a array of values, which would typically be the values you season each row in the table. IsNull() is evaluated for each date value in this case, and generates a array of multiple zeros. Dimensions only display distinct value, so there will only be one row with one zero in the chart.

             

            The measure is calculated over he dimension values. Over the zero value the data set will span over all date values. There is not one distinct date value in the current date field, so the reference to the field without an aggregation function return NULL as an indication to that a distinct value could not be resolved. IsNull() of NULL is most often true, so the measure result becomes true (-1).

             

            Consider that your measure is equal to IsNull(Only([Start Date])). Only([Start Date]) always returns NULL when there referred expression has more than one distinct value. If you select one date, the chart in your example will return false(0) for the measure.

              • Re: isNull different behaviour
                PAOLO MAPELLI

                Hi Toni,

                proposed test scope is to understand the behaviour of isNull() function...

                 

                That's because I'm trying to write down a table with a calculated dimension, that could show a date from another table column where its value is only between "start date" and "end date", or all values when neither start nor end date have been selected.

                  • Re: isNull different behaviour
                    Ruben Marin

                    Hi Paolo, I think than the dimension is returning 0 because it's evaluated for each of the values in the dimension and as thare are values it's not null, so the IsNull() returns zero..

                     

                    In the expression it returns true because it has many different values for the same row, so it ends returning null().

                     

                    If you select a date both should return IsNull()=0; and if you include the expression in an aggregation funtion than returns only one of the different values it also will return false. (ie: Max([Start Date]))