topic Sort by expression in QlikView

Sort by expression

Thu, 04 Feb 2010 23:42:25 GMT

As you can see I have created a bar chart.
Now suppose that I want to order the Sales expression in ascending (like the example) but the Cost expression in descending order.

how can I do this ?
as you can see the dimension is the category, and I don't need to change the sort rule on the dimension but on the expression.

Thanks a lot.

Bye

Sort by expression

disqr_rm — Fri, 05 Feb 2010 04:25:36 GMT

How about putting Cost as negative on the chart with

Sum (CostValue * -1)

Would this be acceptable?

Sort by expression

Fri, 05 Feb 2010 08:45:56 GMT

it works...but it's really bad 😛

Sort by expression

Fri, 05 Feb 2010 08:45:59 GMT

it works...but it's really bad 😛

Sort by expression

disqr_rm — Fri, 05 Feb 2010 18:53:29 GMT

Not that I know of. May be messing around with macros would do the trick, but nothing firm I can think of.

At least you have one option now (better then nothing). 😉

Sort by expression

johnw — Fri, 05 Feb 2010 21:23:51 GMT

Attached is one approach. Instead of using a grouped expression, I create a grouped dimension, then use getcurrentfield() to figure out which "expression" is selected, and calculate it and sort it appropriately. I'm hoping that some future version of QlikView lets us name expression groups and use a similar function to figure out which expression is selected. Or perhaps it already does, and I just don't know about it yet.

Sort by expression

disqr_rm — Fri, 05 Feb 2010 22:39:45 GMT

Wow!!! Excellent John. Thanks.

Sort by expression

Sat, 06 Feb 2010 00:08:36 GMT

wonderful...it works!

thanks a lot :))

Sort by expression

Fri, 12 Mar 2010 17:38:40 GMT

Thanks for sharing. I like the idea.

Any idea if there is an impact on performance with the use of an 'if' statement in the sort?

Sort by expression

johnw — Wed, 17 Mar 2010 16:39:57 GMT


Tyler Waterfall wrote:Any idea if there is an impact on performance with the use of an 'if' statement in the sort?

I haven't done any testing, but I would expect the performance impact to be negligible. Generally speaking, when the IF is outside of the aggregation, it should be evaluated once, and therefore not impact performance. When the IF is inside of the aggregation, it needs to be evaluated multiple times, and can therefore cause performance problems with large data sets. This one is outside of the aggregation, so should be fine in theory.

Edit: Slight misstatement, perhaps. The IF is INSIDE of the aggregation by the dimensions of the chart. What I meant is that it isn't inside of the sum(). I'm not doing a sum(if()). Being evaluated once for each row of a chart isn't typically a problem, but perhaps could be in very large straight tables or pivot tables.