Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
paolo_mapelli
Not applicable

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
Not applicable

Re: isNull different behaviour

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,

Toni_Kautto
Not applicable

Re: isNull different behaviour

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
Not applicable

Re: isNull different behaviour

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
Not applicable

Re: isNull different behaviour

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