Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 '-'.
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.
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)
Hello Mayilvahanan,
Thanks for the reply.
I tried the above expression, its not working.
Hi
If possible, can you create the sample qlikview file with your scenario to proceed further.
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.
You need to wrap your dimension in an aggregation function
=aggr(if(isnull(customer_type), 'Others',customer_type), customer_type)
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.
Possibly you are using other dimensions in your chart
Try adding them to the aggregation; alternatively, share your dashboard if you can
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.
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.