topic Re: Not able to sort correctly a top 10 in a bar chart in QlikView

Not able to sort correctly a top 10 in a bar chart

Thu, 12 Jan 2012 17:13:04 GMT

Hi,

I try to show 10 customers that are sorted by their revenue in ascending order, but only to customers that their revenue was more then 200$.

(so, in other words, I want to show my customers with the lowest revenue, but only to those that their minimum revenue is more then 200$) ,

so i use this expression :

=IF( Sum(revenue) >= 200, Sum(revenue), Null())

and then i sort by setting the Y-Value to Ascending, and in the Dimension Limit tab i choose to show only the smallest 10 values.

but what i get is like this:

all the 9 left empty bars were not supposed to be there, only the right bar stands for the criteria of revenue more than 200$.

Please help me to do this correctly.

Thanks,

Guy

Not able to sort correctly a top 10 in a bar chart

Thu, 12 Jan 2012 17:15:07 GMT

Is the Suppress when Value is Null check box checked for your dimension?

Re: Not able to sort correctly a top 10 in a bar chart

Thu, 12 Jan 2012 17:40:04 GMT

hi,

In presentation sheet select 10 for Max Visible Number

For the expresion : in expressions sheet : if (Amount>200,Amount)

In sort sheet Sort by Y-value Ascending

See in the example

Re: Not able to sort correctly a top 10 in a bar chart

Sun, 15 Jan 2012 10:07:26 GMT

Hi,

Thanks, Its true. But i see now that i uses the following expression:

=IF(Sum(revenue) > 200,Sum(revenue))

becuase i want only the customers that their sum is more the 200$ to be included in the chart.

But this causes to the Others to be added to the chart, so what i see eventually is onlt the Others dimention.

What is wrong with this expression?

Thanks,

Guy

Re: Not able to sort correctly a top 10 in a bar chart

Sun, 15 Jan 2012 14:32:05 GMT

Hi,

I understood the problem. when you use expression like this:

=IF(Sum(revenue) > 200,Sum(revenue))

that means when Sum(revenue) <= 200 the expression returns Null, so the chart does not know how to deal with it correctly. You can try to force the chart to ignore it by checking "Suppress when value is Null" but the chart will continue to see it as values that belong to Others, and you can not sort the chart in ascending manner correctly.

The solution i found was to use a Calculated Dimension where i use the condition:

=If( Aggr(sum(revenue), Customer)>200 ,Customer)

So It is forced that only the customers that have revenue above 200$ to be included in the dimension. Then I check "Suppress When Value Is Null".

In the Expresions Tab there is Sum(revenue).

and eventually in the Demension Limits Tab I choose "Show Only the Smallest 10 Values".

Thats All,

Have a Great Week!