Null Values to be shown as Others in Pie Chart

[sunainapawar]

hello,

I need to show the null values as 'Others' in a pie chart for different customer_types.

i used the below expression, but its not working.

if(isnull(customer_type),'Others',customer_type)

i also tried to use the relative value for the dimension. I gave it as >=1.0%. This works fine. But there is one customer_type, for which the value is <1.0% i.e.,0.3%. This value is also included in Others. I dont want this to be included in Others. Is there any other way to do this. Please suggest.

Please find the attached screenshot.

Measure used is:

Count( {1<card_fYear={"<=$(vMaxYear)"},card_status={'ACTIVE'}>} distinct customer_id)

the Portion in grey color in pie chart must be shown as Others, but rather it just displays a '-'.

[MayilVahanan]

Hi

Might be your customer Type is empty? If so, try like below

if(isnull(customer_type) or len(Trim(customer_type))=0,'Others',customer_type)

[sunainapawar]

Hello Mayilvahanan,

Thanks for the reply.

I tried the above expression, its not working.

[MayilVahanan]

Hi 

If possible, can you create the sample qlikview file with your scenario to proceed further.

[sunainapawar]

Hi,

May be i can show you the table with values.

I need to show the Number of Active Customers. Basically these customers are from 2004 year. I need to show Number of Active Customers from 2004 till present. So i have used below expression.

Count( {1<card_fYear={"<=$(vMaxYear)"},card_status={'ACTIVE'}>} distinct customer_id)

I am getting the data as attached. When i tried the expressions as you mentioned previously its not working.

I tried other way by using relative value option for dimension. I gave relative value as >=1.0%. But here i have one customer type F which has 0.3 % as value. So when relative value is given as >=1.0%, 0.3% is also included in Others.I dont want this to be included in Others. is there any other way to do this?

Thanks in advance.

[lorenzoconforti]

You need to wrap your dimension in an aggregation function

=aggr(if(isnull(customer_type), 'Others',customer_type), customer_type)

[sunainapawar]

Hello  Lorenzoconforti,

Thanks for the reply. The expression is working. It is showing null values as others. But it is taking customer type A and customer type E into null. They are showing as blank now. 

[lorenzoconforti]

Possibly you are using other dimensions in your chart

Try adding them to the aggregation; alternatively, share your dashboard if you can

[sunainapawar]

Hello,

Yes, in the expression i am using the fields card_fyear and card_status are from other tables, and customer_type is from different table.

Count( {1<card_fYear={"<=$(vMaxYear)"},card_status={'ACTIVE'}>} distinct customer_id)

Can it be due to this?

I will create a app and share.

Thanks in advance.

[sunainapawar · Accepted solution]

Hello,

I solved this issue by using Applymap in script.

Table:

Mapping

load customer_id,customer_type

from customer_master;

load *,

Applymap('Table',customer_id,'Others') as customer_type_new

from card_masters;

It is working fine now. Null values are shown as Others.