topic Re: Null Values to be shown as Others in Pie Chart in App Development

Null Values to be shown as Others in Pie Chart

sunainapawar — Sat, 16 Nov 2024 18:51:33 GMT

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 '-'.

Re: Null Values to be shown as Others in Pie Chart

MayilVahanan — Wed, 01 Apr 2020 09:49:39 GMT

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)

Re: Null Values to be shown as Others in Pie Chart

sunainapawar — Wed, 01 Apr 2020 10:33:18 GMT

Hello Mayilvahanan,

Thanks for the reply.

I tried the above expression, its not working.

Re: Null Values to be shown as Others in Pie Chart

MayilVahanan — Thu, 02 Apr 2020 07:45:08 GMT

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

Re: Null Values to be shown as Others in Pie Chart

sunainapawar — Thu, 02 Apr 2020 11:56:01 GMT

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.

Re: Null Values to be shown as Others in Pie Chart

lorenzoconforti — Thu, 02 Apr 2020 13:34:07 GMT

You need to wrap your dimension in an aggregation function

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

Re: Null Values to be shown as Others in Pie Chart

sunainapawar — Fri, 03 Apr 2020 06:07:01 GMT

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.

Re: Null Values to be shown as Others in Pie Chart

lorenzoconforti — Fri, 03 Apr 2020 09:21:28 GMT

Possibly you are using other dimensions in your chart

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

Re: Null Values to be shown as Others in Pie Chart

sunainapawar — Fri, 03 Apr 2020 10:32:24 GMT

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.

Re: Null Values to be shown as Others in Pie Chart

sunainapawar — Mon, 06 Apr 2020 05:44:04 GMT

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.