Need to display missing data as blank or n/a

[Zak2]

Hi,

I have some survey data in a table (not pivot). Some data from a previous year is correctly not available, so those cells are empty in the excel file loaded into qlik sense. However, those data points are shown as 0% in qlik sense, which is incorrect, and will be misleading to users. In the presentation section there is no option to deal with nulls, etc.

How can I ensure empty cells are displayed instead of 0%? 0% is correctly displayed in some cells, so I don't want to remove all instances of 0%.

Hope that makes sense.

Thanks

[rwunderlich · Accepted solution]

I imagine your problem is caused by using an aggregation function like Sum() against the field.  Sum() treats blanks as zero so: Sum('') = 0.  You can work around this issue with  something like:

if(Trim(Value) = '', Value, Sum(Value))

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[HeshamKhja1]

If you look into your data model, are they null in the model?

If they are not null in the model but appear as empty spaces, then maybe you can fix it by adding the line below before loading your excel.

SET NullValue = '';

Let me know if it works

[Zak2]

You imagined correctly Rob. I was able to remove sum totally, as no aggregation was required. Your solution also worked, so I'll remember that for future reference.

Thank you so much!

[Zak2]

I had tried this previously, but it didn't work. As Rob points out, the issue is with sum(), which treats missing data as 0. Thanks for your response.