topic Re: Sorting in unique manner in QlikView

Sorting in unique manner

Fri, 24 Feb 2017 04:34:11 GMT

Hi Team,

I have a below unique sort requirement wherein the client has requested the below logic to be applied.

The tables should show the sales in descending order but should keep the Product - 'OTHERS' as the last product even if the Sales may be max.

Product	Sales
X	200
B	130
H	100
D	98
OTHERS	240

Please advise how to resolve this. In Sort tab, under expression, tried the below expression

if( [Product] <> 'OTHERS',rank([Sales]). Below is the result but Product 'Others' remains at the top instead of at the bottom.

Product	Sales
OTHERS	240
X	200
B	130
H	100
D	98

Re: Sorting in unique manner

Chanty4u — Fri, 24 Feb 2017 04:39:17 GMT

try below

=if(Match(PRODUCT,'OTHERS','X','B','H','D'),Sales)

Re: Sorting in unique manner

Fri, 24 Feb 2017 04:58:31 GMT

Hi,

The Sales will change every week and as per the Sales value the table should be sorted in descending order and 'Others' must remain the last row ignoring the above ranking. I have Regions in another listbox, on selection, the Products will either go above or below as per the sales value hence cannot explicitly define the Product names in Match function. The above solution does not give my required result.

Re: Sorting in unique manner

Sat, 25 Feb 2017 06:55:33 GMT

Could someone please help with a solution to the sorting order issue.

Thanks

Shinu

Re: Sorting in unique manner

ecolomer — Sat, 25 Feb 2017 07:04:03 GMT

If OTHERS is the result of the accumulation of the remaining unrelated values, the solution can be ordered by Y value but by setting a limit of 5 values and the rest in a total that is called OTHERS

Re: Sorting in unique manner

shivkumar300 — Sat, 25 Feb 2017 07:13:18 GMT

Hi,

try this

if([Product] <> 'OTHERS','1',if([Product] ='OTHERS',rank([Sales])))

Re: Sorting in unique manner

antoniotiman — Sat, 25 Feb 2017 09:19:56 GMT

Hi Shinu,

in Sort Expression try

If(Product <> 'OTHERS',Sum(Sales),0)

Regards,

Antonio

Re: Sorting in unique manner

petter — Sat, 25 Feb 2017 12:55:29 GMT

Using this in your expression for Sort by should work well:

If( Product = 'OTHER' , 0 , -Sum(Sales) )

Re: Sorting in unique manner

Sun, 26 Feb 2017 14:30:23 GMT

Thank you so much. You are a genius.