Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paolo_mapelli
Creator II
Creator II

isNull different behaviour

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

4 Replies
jayaseelan
Creator III
Creator III

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,

ToniKautto
Employee
Employee

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.

paolo_mapelli
Creator II
Creator II
Author

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.

rubenmarin

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]))