topic Re: Suppressing Null/Zero Value (Qlik Sense) in QlikView

Suppressing Null/Zero Value (Qlik Sense)

blpetosa — Thu, 19 Jul 2018 13:57:29 GMT

Hello Everyone,

I am having an issue suppressing null or zero values in Qlik Sense. Here is an example that someone posted about 4 years ago. My use case is much more complex, but I believe the answer will be the same for the example below.

I have a straight table where I am simply showing sum of sales for the days of the week. If there are no sales for a day of the week, like Monday, how can I force that day to appear with no data or zero?

Sun 10

Mon

Tues 15

Wed 20

Thurs 5

Fri 11

Sat 22

This was the correct answer at the time:

In dimensions tab:

- check "show all values"

- uncheck "supress when value is null"

In presentation tab:

- uncheck "supress zero values"

I don't see these options in Qlik Sense, and looking for some help.

Thanks in advance,

Ben

Re: Suppressing Null/Zero Value (Qlik Sense)

sunny_talwar — Thu, 19 Jul 2018 14:02:40 GMT

In click sense, you can do this by either unchecking 'include zero values' under the dimension or under Add-Ons-> Data handling

Re: Suppressing Null/Zero Value (Qlik Sense)

blpetosa — Thu, 19 Jul 2018 14:37:44 GMT

I tried un-checking, and checking that box, but nothing changes. I believe I would actually want to have that box checked, so Monday (example above), still shows up with a null value. Then, at least, I could most likely use an IF statement to change the null value to whatever I want.

Re: Suppressing Null/Zero Value (Qlik Sense)

sunny_talwar — Thu, 19 Jul 2018 14:41:51 GMT

Yes, you are right... I somehow thought the opposite... so selecting 'include zero value' under AddOn - > Data Handling didn't work?

Re: Suppressing Null/Zero Value (Qlik Sense)

blpetosa — Thu, 19 Jul 2018 15:14:06 GMT

No, unfortunately. My situation is a bit more complex, so maybe it is more involved. We have users answering surveys on our Website. If there are 5 possible answers for a question, but nobody answers 1 of them, I only see data for 4 of the answers. My table for this example is here:

Order (Dim) Answer (Dim) "Count" (count(distinct solution_id))

1 A 11

2 B 7

3 C 45

5 E 4

I need Order 4/Answer D to show up as null/zero

Re: Suppressing Null/Zero Value (Qlik Sense)

blpetosa — Thu, 19 Jul 2018 15:18:27 GMT

* For the "Count" measure

Re: Suppressing Null/Zero Value (Qlik Sense)

sunny_talwar — Thu, 19 Jul 2018 15:19:39 GMT

May be try adding this to your expression and see if this helps

Expression + Sum({1} 0)

Re: Suppressing Null/Zero Value (Qlik Sense)

blpetosa — Thu, 19 Jul 2018 15:54:31 GMT

It doesn't work. I have a feeling this may be a data issue, before it gets to Qlik. I am unable to get the desired output even when I query the data warehouse directly.This may be a job for Python, before it even gets to Qlik. I will let you know what comes of it. Thank you for the help, Sunny.

Re: Suppressing Null/Zero Value (Qlik Sense)

sunny_talwar — Thu, 19 Jul 2018 15:55:50 GMT

Or may be you need this

Generating Missing Data In QlikView