Problem with sort order

[tduarte]

Hi,

I'm trying to sort a table where regardless of the total, when Category is 'Other' then it should be at bottom of the table. The other values should be sorted by total.

Sort-> Sum(Units):

Category	Units	Sort
Notebooks	400	400
Tablets	300	300
Other	200	200
Desktops	100	100

Sort-> Sum(Units)*if(Category='Other',-1,1) :

Category	Units	Sort
Notebooks	400	400
Tablets	300	300
Desktops	100	100
Other	200	-200

So far so good. Now, the problem is that is some cases the total values are negative which put Other at the top (-200*-1=200) and not bottom.

Category	Units	Sort
Other	-200	200
Desktops	-100	-100
Tablets	-300	-300
Notebooks	-400	-400

What would be a possible sort expression that suits both scenarios?

[swuehl · Accepted solution]

Maybe use a static sort number for 'Others':

if(Category = 'Other', -99999999, Sum(Units) )

[#3]

Sum(Units)*if(Category='Other',-1*sign(Sum(Units)),1)

maybe?

[mphekin12]

You can try using the DUAL keyword in your load to assign both a string and a numeric value to your Category.  Then you can set the sort order of Category to the Numeric Value.  I hope this helps!

[#5]

Hi,

it depends on the cardinality of your data. This algorithm could work is fabs(sum(Units)) >=1:

Sum(Units) * if (Category='Other', (1 / Sum(Units))* -1, 1)

Regards

Sacho

[tduarte]

Oscar Pizarro wrote:

Sum(Units)*if(Category='Other',-1*sign(Sum(Units)),1)

maybe?

Thanks but it doesn't work for the all negative situation.

[tduarte]

swuehl wrote:

Maybe use a static sort number for 'Others':

if(Category = 'Other', -99999999, Sum(Units) )

Thanks swehl.

It does the job and it's optimised.

[tduarte]

mphekin12 wrote:

You can try using the DUAL keyword in your load to assign both a string and a numeric value to your Category.  Then you can set the sort order of Category to the Numeric Value.  I hope this helps!

Not sure if it would aplicable on my case because the values of Category are not static and will change during reloads.

Am I correct?

[mphekin12]

You are correct if your Categories are not static. What about if you used a sort flag like this:

if(Category='Other', -1, 0) as SortLastFlag

You can then use this field as a hidden dimension in your table/chart and use this as your primary sort dimension.

[tduarte]

This what I had before posting this discussion:

Script:

if(Category='Other', -1, 1) as SortCategory;

Sort order expression:

Sum(Units)*SortCategory

But like I explained, there's the issue with the negative totals.

All I want is whenever the value 'Other' is found, it must be last in sort order regardless of its total.