Formula in Bar chart

[#1]

Hi,

I have a expression as below in the bar chart.

=((sum(if(KPI='TTL', Act_Data))-sum(if(KPI='OT', Act_Data)))/(sum(if(KPI='TTL', Act_Data)))). If there is no data for kpi='OT', Qlikview assigns a value of 0 and calculates the formula and it displays '100%' which is wrong and misleads the user.The dimension is a cyclic group of month and year.

I had the same problem in the table box, but I changed the formula as below and it worked. It will display '-' when there no value for KPI='OT'
=((mode(if(KPI='TTL', Act_Data))-mode(if(KPI='OT', Act_Data)))/(mode(if(KPI='TTL', Act_Data)))). Here the dimension is month, Year,region and country, and it displays '-' for a country if there is no value for 'OT' .

When I applied the same formula in the bar chart, it is not displaying for the whole region, eventhough only one country is not having the value for 'OT'. How to modify the formula, so that when there is no data, I don't want it to get dispalyed in the bar chart.

[Oleg_Troyansky]

I'm not sure how Mode() can help - it's returning the most commonly occurring data... I'd stick to your first formula and make changes to it:

You can try using a calculated dimension to exclude those Countries with no data for OT:

if(KPI='OT', Country)

and select "Suppress When Value is Null" to eliminate those countries with no data for OT.

regards,

Oleg

[#3]

Hi Oleg,

I won't be able to write a calculated dimension, because my dimension is a cyclic group of Month, quarter and year. When I apply, 'Suppress when value is Null' to the cyclic group, it does not help. Is there any other way of getting it work.

Santhosh

[Oleg_Troyansky]

I thought your dimensins were Regions and Countries... Could you post an example, that we could use to review the problem?

Oleg

[#5]

I have attached the model in the main message. If you see country 'Asia', the bar chart should go off blank, but it is displaying 100%. And if you see the straight table, it displays '-' for the %OT for the country 'Asia'.