Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Aug 16, 2021 4:57:00 AM
Aug 16, 2021 4:57:00 AM
If you use the IsNull() function in the dimension expression in Qlik Sense and in the calculated dimension in QlikView, you will notice that they behave differently.
For example, we have a dataset that contains null values and we create a Table chart in Qlik Sense and Straight table in QlikView that displays the values. Then we add a new dimension to the Table chart in Qlik Sense with expression "IsNull(MyData)" and we add a new calculated dimension in QlikView Straight table with the same expression "IsNull(MyData)".
We will notice that using the IsNull() function in the Qlik Sense dimension expression will return 0 for values that are not null and null for values that are null. However, if we use the same function IsNull() for the same dataset in QlikView calculated dimensions, we will notice that it returns 0 for not null values and -1 for null values.
QlikView:
Qlik Sense Dimension Expression:
Environment
Resolution
Qlik Sense derives expression-based dimensions as fields on the fly, which is different from the calculated dimensions in QlikView. Additional information can be found in article “Calculated Fields”.
Expression-based dimension in Qlik Sense will be evaluated as if it was added in the data model reload time, such as:
LOAD
CompanyName,
IsNull(CompanyName)
[…]
The null values in the CompanyName dimension represent that the data points do not exist in the data, therefore the fields on the fly can’t generated the value for the missing CompanyName(s), which is the reason that we are getting a null value in the field, on the fly, when using the expression in the dimension.
Generally it is not a good practice to add expression in dimensions, so it is advisable to consider moving this comparison logic to measure instead, to ensure that the IsNull() function will return -1 for Null values and 0 for not Null values.
Qlik Sense expression in Dimension vs Measure:
