Sort with rank dynamically

[aheavy95]

Hi

In a straight table I added a measure that ranks the rows based on an expression (last column)

The Expression currently ranks the table based on a specific measure (sum(SUM_TRHE_Transaction)) and it works well.

What I'm trying to achieve is, creating a measure that ranks the table based on the measure selected to rank the table.

For example, in the screenshot, the table is sorted by 'Sales Net' measure (blue arrow), but since the rank measure is by a different measure, it is irrelevant.

How can I make the ranking expression:   rank(total sum(SUM_TRHE_Transaction)) dynamic, to be based on the measure selected to sort by the user (the normal header-double click)?

Thanks

[rubenmarin · Accepted solution]

Hi, I don't know if ther is a better way that add a variable object with buttons, one for each sort option, so the user can select the sort, each button sets the variable to a value, and you can use this variable value to set the rank expression (you can have columns for each each rank and hide/show based on the variable value).

Also the sort option of the store can be set as an expression based on this variable value.

[aheavy95]

thanks fro your response

Is it possible to achieve this under one variable input visualization (or some other chart) 

that will be presented as a dropdown list? There will be around 10 different measures so presenting 10 buttons at once is not preferable.

Also, could you provide the steps for implementing this?

[rubenmarin]

Hi, when you use the variable object (in the dashboard bundle) there are different layout options, you can show as buttons or as a drop-down. Set he different selct values and the value the variable will store for each value.

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Visualizations/DashboardB...

Add the different rank meassures to the table and use the 'show column if' property of each rank to only show when the variable has the value associated with his rank.

If the variable vSortTable has 'Quantity' with value 1 and 'Sales' with 2. Add the rank by Quantity with condition vSortTable=1 and the rank by Sales column will have vSortTable=2. The selection of the user will show one column or another.