topic Re: Expression for Labeling and Selecting Null Values in QlikView

Expression for Labeling and Selecting Null Values

ltc — Fri, 25 May 2018 16:48:35 GMT

Hi,

I have a column name "CCaR User DD2875 is Valid" which has Yes, No, and Null values. I'd like to label the null values as "Empty" and make the null values selectable. What is the expression for this?

Thank you

Re: Expression for Labeling and Selecting Null Values

adityaakshaya — Fri, 25 May 2018 17:32:08 GMT

Hi Lea,

Please use the below expression in your Dimension : Field, it will work for you.

if([CCaR User DD2875 is Valid]='','Empty',[CCaR User DD2875 is Valid]) and in the label, you can name it as per the requirement.

Regards,

Akshaya

PS- if you find response as correct and Helpful, Please mark it as correct and helpful

Re: Expression for Labeling and Selecting Null Values

ltc — Fri, 25 May 2018 17:40:12 GMT

Thank you for the response! Unfortunately, it didn't seem to work. The null values are still appearing as "-".

any suggestions?

Re: Expression for Labeling and Selecting Null Values

m_perreault — Fri, 25 May 2018 17:40:53 GMT

If(isnull([CCaR User DD2875 is Valid]),'Empty',[CCaR User DD2875 is Valid])

Re: Expression for Labeling and Selecting Null Values

adityaakshaya — Fri, 25 May 2018 17:42:58 GMT

You can try like below

if(isnull([CCaR User DD2875 is Valid]),'Empty',[CCaR User DD2875 is Valid])

if(Len([CCaR User DD2875 is Valid])=0,'Empty',[CCaR User DD2875 is Valid])

Regards,

Akshaya

Re: Expression for Labeling and Selecting Null Values

ltc — Fri, 25 May 2018 17:56:01 GMT

Thank you!

It did label the null values as Empty and I am able to select the "Empty" label on the donut chart.

How can I get the null values to also be included in the KPI chart? Without filters selected, it should show 13607 of 13607 users. However, it shows 11,050 because it's not counting the null values.

Here is my current KPI expression. It's just a count.

But I need it to include null values. What would the expression be?

Thank you!

Re: Expression for Labeling and Selecting Null Values

ltc — Fri, 25 May 2018 17:56:56 GMT

Thank you so much. It worked, but there was another person who replied before you and I used his expression. I wish I could mark both as correct. If there is a way to do so, please let me know.

Re: Expression for Labeling and Selecting Null Values

m_perreault — Fri, 25 May 2018 18:06:00 GMT

My recommendation would be to move this logic to your script

If(isnull([CCaR User DD2875 is Valid]),'Empty',[CCaR User DD2875 is Valid]) as [CCar User DD2875 is Valid]

Re: Expression for Labeling and Selecting Null Values

ltc — Fri, 25 May 2018 18:10:18 GMT

Makes sense! Although I am not the owner of the app, so I can't open the data load editor.

I am just working on a sheet for myself, so I don't think I have a choice but to edit the KPI expression.

Is there a way to edit my current KPI expression to include null values?

My current expression is a count:

Count([CCaR User DD2875 Is Valid])

I don't know how to add an IF statement if I have a count.

Thank you again.

Re: Expression for Labeling and Selecting Null Values

vishsaggi — Fri, 25 May 2018 18:10:40 GMT

Try this?

= Sum(Aggr(Count(If(isnull([CCaR User DD2875 is Valid]),1,[CCaR User DD2875 is Valid])), [CCaR User DD2875 is Valid]))

Re: Expression for Labeling and Selecting Null Values

ltc — Fri, 25 May 2018 18:13:00 GMT

wow! thank you so much! that worked

Re: Expression for Labeling and Selecting Null Values

vishsaggi — Fri, 25 May 2018 18:24:15 GMT

No problem.