How to include Null Values in 'Others' rather than excluding them in qlik sense.

[Anonymous]

I want to show a pie chart with top 5 countries by sales. I have some null Values and unknown in countries which i want them to be a part of my calculation.But in some cases Nulls and unknowns also comes in my top 5 apart from others. I want those nulls and Unknown to be in calculations but these nulls and unknowns should be included in Others. How can i do it in qlik sense.

[sunny_talwar · Accepted solution]

I suggest using a calculated dimension instead of using the inbuilt

[Anonymous]

Hi sunny,thanks for the answer . This works but what do i do for Unknown?? Will

Country={"*"},Country-={'Unknown'}    work??

I am asking this because this formula doesnt work with my desktop version but it works for server i dont know why may be because of version issue.

[kaushiknsolanki]

Hi,

I would suggest you to give null and unknown a name in load script to ease the expression on charts. Like below.

Load *,If(Isnull(Country) or Country = 'Unknown','Others',Country) as NewCountry

From xyz;

Now use this new field as your dimension.

Regards,

Kaushik Solanki

[sunny_talwar]

May be this

Country={"*"}-{'Unknown'}