Sorting in unique manner

[#1]

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

[Chanty4u]

try below

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

[#3]

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.

[#4]

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

Thanks

Shinu

[ecolomer]

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

[shivkumar300]

Hi,

try this

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

[antoniotiman]

Hi Shinu,

in Sort Expression try

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

Regards,

Antonio

[petter · Accepted solution]

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

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

[#9]

Thank you so much. You are a genius.