topic Ignoring zero and null values in chart when considering smallest values for a chart in QlikView

Ignoring zero and null values in chart when considering smallest values for a chart

Wed, 24 Sep 2014 21:24:32 GMT

I need a chart to show yield by smallest values ignoring zero. I've tried suppressing zero/null values, but it seems to choose them first, then suppress them. For example, if I want the smallest 6 while suppressing zeros and nulls, it will get those 6, then if 2 of those 6 are zero or null, it will hide those 2 and show only 4. I need it to ignore those and grab the next 2 non-zero/non-null values if they exist and display 6. I've also tried negating the expression and taking the largest, but even with formatting the number to hide the negative sign, it still shows negative percentages on the axis.

I made a small sample document to demonstrate. Are there any workarounds for this?

Thanks in advance,

Alex

Re: Ignoring zero and null values in chart when considering smallest values for a chart

simenkg — Wed, 24 Sep 2014 21:41:21 GMT

Use the expression:

if(rank(-(Sum([Quantity Out])/sum([Quantity In])),0)<=3,sum([Quantity Out])/sum([Quantity In]))

Turn off dimension limits, but turn on Suppress Zero-Values.

Hope this helps
SKG

Re: Ignoring zero and null values in chart when considering smallest values for a chart

phaneendra_kunc — Wed, 24 Sep 2014 21:43:40 GMT

Try this expression

sum({<[Quantity Out]-={0},[Quantity In]-={0} >}[Quantity Out])/sum({<[Quantity Out]-={0},[Quantity In]-={0} >}[Quantity In])

Re: Ignoring zero and null values in chart when considering smallest values for a chart

Thu, 25 Sep 2014 13:52:39 GMT

Thanks. Worked perfectly