topic Problem with sort order in QlikView

Problem with sort order

tduarte — Mon, 20 May 2013 14:00:10 GMT

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?

Re: Problem with sort order

swuehl — Mon, 20 May 2013 14:20:24 GMT

Maybe use a static sort number for 'Others':

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

Re: Problem with sort order

Mon, 20 May 2013 14:20:37 GMT

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

maybe?

Re: Problem with sort order

mphekin12 — Mon, 20 May 2013 14:22:38 GMT

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!

Re: Problem with sort order

Mon, 20 May 2013 14:41:58 GMT

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

Re: Problem with sort order

tduarte — Mon, 20 May 2013 15:29:52 GMT

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.

Re: Problem with sort order

tduarte — Mon, 20 May 2013 15:32:43 GMT

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.

Re: Problem with sort order

tduarte — Mon, 20 May 2013 15:45:18 GMT

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?

Re: Problem with sort order

mphekin12 — Mon, 20 May 2013 15:53:33 GMT

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.

Re: Problem with sort order

tduarte — Mon, 20 May 2013 16:02:47 GMT

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.

Re: Problem with sort order

mphekin12 — Mon, 20 May 2013 17:28:58 GMT

Please take a look at the sample I have attached. I am not using the flag in the calculation at all. I'm only using it as a dimension for sorting.

Re: Problem with sort order

tduarte — Tue, 21 May 2013 08:27:45 GMT

Thanks mphekin12,

That works but not applicable in my situation because my object is pivot table, meaning I cannot hide the dimension.