topic Re: Dynamic sort on dynamic dimensions in QlikView

Dynamic sort on dynamic dimensions

adamdavi3s — Thu, 24 Nov 2016 13:00:24 GMT

Dear All,

Creating a 'diy' reporting section which allows users to select dimensions and expressions in variables, these variables are then used in a bar chart (for example), nothing new or fancy here.

I want to give the user the ability to sort the x-axis by expression value or dimension A-Z or Dimension 1-9 WITHOUT the use of macros.

Is this possible within the expression sort option? I can't seem to find a definitive answer on this.

I stress again and clearly: WITHOUT THE USE OF MACROS

Re: Dynamic sort on dynamic dimensions

sunny_talwar — Thu, 24 Nov 2016 13:08:42 GMT

See if this takes you any closer to what you are looking for.

Sort expression:

=Pick(Match(vSort, 'By exp', 'By A-Z', 'By 1-9'), -sum($(v_exp)), Ord($(v_dim)), Ord($(v_dim)))

Re: Dynamic sort on dynamic dimensions

adamdavi3s — Thu, 24 Nov 2016 13:15:16 GMT

Excellent thank you, wasn't aware of the ORD function

Re: Dynamic sort on dynamic dimensions

sunny_talwar — Thu, 24 Nov 2016 13:18:45 GMT

Ord is the opposite of Chr(). But the issue is that it only give the value for first letter. So abc = 97 and acb = 97 (anything starting with a is 97.

So you can further improve on this expression by summing the Ord value for all the letters. The above was just to give you a start.

Re: Dynamic sort on dynamic dimensions

adamdavi3s — Thu, 24 Nov 2016 13:30:37 GMT

Its great thank you, just what I needed to get started... I figure I can also add some tests to automatically determine if the field is numeric etc thanks!