Numeric and Custome sort

[jonathan_dau]

Hi guys,

A question concerning column sort for straight table.

I have a table with several market shares.

I want to sort these market shares by numeric value.

But I also want to keep the line Other/Fcst at the very end of the table.

Is there a way to mix both Custom sort and Numeric sort??

I have tried using Match() function and numeric sort in the sort parameters but without success.

Any clue is most welcome.

Cheers.

Jonathan

[alexandros17]

in the sort tab:

1) Vendor: use the expression and set it to: if(Vendor='Other/Fcst',2.1)

2) in the value field set numeric order

jhopeit helps

[Gysbert_Wassenaar · Accepted solution]

Add another expression: if(Vendor='Other/Fcst',1,0). Then sort first numerically on the new expression and then on the market share expression. Then hide the new expression. See attached example.

[Anonymous]

Jonathan,

You can use MarketShare as Dimension rather than Expression, if it is a field in your data model.  In this case you can sort it by Expression:

if(Vendor='Other/Fcst', -1, MarketShare)

If it is not a field, you still can use this solution creating calculated dimension, and the sort expression will be more complex.

The limitation of this solution is that it works only for Decending order.  In Accesnding, "Others" go on top.  Obviously for Accending you need something like this to move it to the bottom:

if(Vendor='Other/Fcst', 100, MarketShare)
So, my suggestion is rather a "hint" than "solution"

Regards,

Michael

[#5]

Hi!

Try

IF(Total<>'Other',Total) the expression of the classification. Look at the attached example.

Rebeca

[Anonymous]

in sort tab ..select expression (sort  by expression ) and inculde marketshare field in it

hope this helps

Thanks

[swuehl]

Have you tried your mix like this (use as sort by expression for your dimension):

=if(match(Vendor,'Other/Fcst'), 1000, sum(VALUE) )

VALUE is the field name you are using to calculate your share from (I assumed you are using something like

=sum(VALUE) / sum(total VALUE)

for the share calculcation.

[jonathan_dau]

Hey Gysbert,

Thanks I used your solution and it works great !!

Thanks to all of you guys for your answers, I didn't take a clear look at all of them but I am sure they're good as well.

[jonathan_dau]

Hi Michael,

Thank you for your answer I was thinking of using Market Share as a dimension or calculated dimension, but it seems a little bit too complex.

Thanks for the hint anyway.

Regards

Jonathan