topic Re: Disregard the null values in expression with dimension limits in QlikView

Disregard the null values in expression with dimension limits

Anonymous — Mon, 05 May 2014 20:28:31 GMT

Hi,

I have a table,

LOAD * INLINE [

Country, Sales, Profit

USA, 10000, 2000

UK, 9000, 1000

FRANCE, -, -

INDIA, 30000, 4000

PAKISTAN, 3000, 5500

];

Now, i have created a bar chart, with country dimension and sum(sales)/sum(Profit) as expression. I gave the dimension limits to show largest 4 (Uncheck the Show Others option). Now, as France has no values in the sales and profits, i don't need FRANCE to appear in the chart (so, i check the Supress Zero Values option in presentation), instead i need the next country, that is PAKISTAN to appear as 4th bar, when i sort the chart by y value descending.

Any suggestions?

I want the expression to disregard the null values while evaluating the expression for this. Thoughts?

Re: Disregard the null values in expression with dimension limits

Anonymous — Mon, 05 May 2014 20:37:12 GMT

Here is the sample app

Re: Disregard the null values in expression with dimension limits

hic — Mon, 05 May 2014 20:40:07 GMT

You need to post some data. If I load your data and create your bar chart, I get this at first try

France is automatically removed. And that's what you want, or...

HIC

Re: Disregard the null values in expression with dimension limits

Anonymous — Mon, 05 May 2014 20:44:17 GMT

I used the dimension limitations, to show the largest 4 and uncheck the others. Then it is giving only first 3 bars.

Re: Re: Disregard the null values in expression with dimension limits

Mon, 05 May 2014 20:46:10 GMT

Here it is...!

Re: Re: Re: Disregard the null values in expression with dimension limits

Anonymous — Mon, 05 May 2014 20:50:51 GMT

If i have some more data like the below:

LOAD * INLINE [

Country, Sales, Profit

USA, 10000, 2000

UK, 9000, 1000

FRANCE, -, -

GERMANY, 2500, 2500

INDIA, 30000, 4000

PAKISTAN, 3000, 5500

];

Now i get 0 for GERMANY Sum(Sales)-Sum(Profit) too. So, i need this to appear in the chart as 0 on bar. But, as France doesnot have the data, i don't need France to appear in the chart.

In this case, if we give if(expression<>0, expression), i will miss Germany. I need Germany and don't need France

Re: Re: Re: Re: Disregard the null values in expression with dimension limits

Mon, 05 May 2014 20:56:41 GMT

Here it is....!

Re: Re: Re: Disregard the null values in expression with dimension limits

Mon, 05 May 2014 21:39:12 GMT

Here...!

use 'Set NullInterpret = -;

Re: Disregard the null values in expression with dimension limits

suheshreddy — Tue, 06 May 2014 00:42:39 GMT

Here it is