topic Re: Numeric and Custome sort in QlikView

Numeric and Custome sort

jonathan_dau — Mon, 15 Apr 2013 14:14:19 GMT

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

Re: Numeric and Custome sort

alexandros17 — Mon, 15 Apr 2013 14:27:33 GMT

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

Re: Numeric and Custome sort

Gysbert_Wassenaar — Mon, 15 Apr 2013 14:39:26 GMT

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.

Re: Numeric and Custome sort

Anonymous — Mon, 15 Apr 2013 14:40:54 GMT

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

Re: Numeric and Custome sort

Mon, 15 Apr 2013 14:43:35 GMT

Hi!

Try

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

Rebeca

Re: Numeric and Custome sort

Anonymous — Mon, 15 Apr 2013 14:48:09 GMT

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

hope this helps

Thanks

Re: Numeric and Custome sort

swuehl — Mon, 15 Apr 2013 15:13:22 GMT

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.

Re: Numeric and Custome sort

jonathan_dau — Mon, 15 Apr 2013 15:19:48 GMT

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.

Re: Numeric and Custome sort

jonathan_dau — Mon, 15 Apr 2013 15:25:19 GMT

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