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

[#1]

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

[#2]

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

[#3]

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

[#4]

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

[#5]

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!