topic Re: dynamic sort order for expressions in QlikView

dynamic sort order for expressions

michael_klix — Thu, 14 Apr 2016 05:50:04 GMT

Hello,

I have various measurements with a flag "high is good" and country as a dimension.

When presenting them in a bar chart, I want them to sort from "bad to good", meaning the worst are left and the better ones are to the right and potentially I have to scoll to see them.

As mentioned before, with the flag "high is good" altering, some need to be sorted ascending and some descending.

How can I make this depending on the flag?

Hope this was well explained.

Keep Qliking

Michael

Re: dynamic sort order for expressions

Chanty4u — Thu, 14 Apr 2016 05:55:15 GMT

in sort tab...

Expression:

=Match(FieldName,' A','b','c') lik so on

or u wan load order ascending or desc chk that

Re: dynamic sort order for expressions

Kushal_Chawda — Thu, 14 Apr 2016 05:57:05 GMT

Go to Bar chart properties-> Sort Tab-> Sort it by expression ascending or descending

or sort it by Y-Value

Re: dynamic sort order for expressions

tresB — Thu, 14 Apr 2016 05:59:18 GMT

Check this great blog: Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level) by cschwarz

Re: dynamic sort order for expressions

Thu, 14 Apr 2016 06:13:32 GMT

Hi Michael

U can use the Match Function, it returns a 1 if a value in the list you specify is found and 0 if the value was not found

=Match(flag, 'high is good') will return 1 for all the rows that contains 'high is good' and they will be first if you sort by expression showed in Chanty's post

Re: dynamic sort order for expressions

sujeetsingh — Thu, 14 Apr 2016 06:19:56 GMT

You can have a number generated in the script itself to sort it.

Re: dynamic sort order for expressions

HirisH_V7 — Thu, 14 Apr 2016 06:27:45 GMT

Hi,

Look into this,

Sorting in qlikview

-Hirish

Re: dynamic sort order for expressions

marcus_sommer — Thu, 14 Apr 2016 06:48:47 GMT

Hi Michael,

I have done such interactive sorting with a sort-expression like:

YourChartExpression - $(vSortDirection)

where vSortDirection contained either 0 or a very high value like 10000000000 which will be reverse the sorting. You could set the variable with a inputbox or a button and as alternatively you could also use a field-selection maybe from a small inline table like:

SortDirection:

Load * Inline [

Direction, SortValue

Ascending, 0

Descending, 1000000000

];

and your sort-expression could be:

YourChartExpression - SortValue

- Marcus

Re: dynamic sort order for expressions

michael_klix — Thu, 14 Apr 2016 11:14:37 GMT

Hi Markus,

just the right hint, but i do it even more simple now:

The wayto revert the sorting order is to multiply the expression value with (-1).

As my flag "High is good" unfortunately is (0,1), I use the sorting Expression

= (2*flag_High_Is_Good -1) //this is -1 for high is good=0

* MyExpression

That works perfectly. Does it make sense this way?

If someone want to add a user interaction capability to change the sorting in a chart, then a button to toggle a variable (-1,1) would be appropriate as well, of course.

Thanks a lot for all contributions.

Keep Qliking

Michael

Re: dynamic sort order for expressions

marcus_sommer — Thu, 14 Apr 2016 11:24:09 GMT

Hi Michael,

it's a great solution - I like it.

- Marcus

Re: dynamic sort order for expressions

michael_klix — Thu, 14 Apr 2016 11:24:57 GMT

Hi Chanty

(and all who followed up later on the match option),

thanks for the reply(s). However I dont think that match would work as the available Country names (Dimension) are not known to me. In addition to be honest, I did not understand how match could help out here.

I dont want to open another question, but just to clarify the problem, here a draft table:

high is good	1	0
country	kpi1	kpi2
a	98	10
b	95	9
c	93	8
d	92	7
e	90	6

When displaying only one KPI in a bar chart, I want KPI1 to be sorted ascending and KPI2 to be sorted descending. So always the worst is left.

Proposed solution is to simply multiply a KPI with (-1) if the "high is good" flag is 0 and use ascending as default sorting order.

rgds

Michael

Re: dynamic sort order for expressions

Chanty4u — Thu, 14 Apr 2016 11:26:31 GMT

cheers you got the solution