Sorting Problem for Straight Table

[#1]

Hi,

I have a straight table. I have some dimensions and expressions. Expressions examples: rowno(total), sum(margin)

I need to sort the table in descending order by sum(margin) expression. However, sorting is not working at all unless I remove rowno(total) expression.

Is there any way to sort the table AND have a column which number all the rows. Thanks

[sujeetsingh]

Use sum(margin) as sort expression

[vikasmahajan]

Qlikview provide intractive sort for Straght Table , Just Double click on column which you want to sort.

Automatically sort will happen.

Good Luck.

Vikas

[#4]

interactive sort doesn't work, and using options from Sort tab doesn't do anything either. It only works if I remove rowno() expression but I need to have both expressions in the table

[rwunderlich · Accepted solution]

Sorting of expression columns is disabled if you use rowno() or other inter-chart functions anywhere in the chart. Not sure what the workaround to get a row number would be, Maybe someone else has an idea.

From the Help:

Note!

Sorting on y-values in charts or sorting by expression columns in straight tables is not allowed when Chart Inter Record functions are used in any of the chart's expressions. These sort alternatives are therefore automatically disabled.

-Rob

http://robwunderlich.com

[notlarrybell]

My workaround for this is as follows:

Create a sort by field that references a variable or number of variables in the body of the expression. Use that variable to build a reference to the title of another field.

So for example, if you have two fields, Revenue and Costs, you create a variable vSortField which resolves to either 'Revenue' or 'Costs' using some buttons. Make sure the values of the variable settings match the labels of the other columns.

Then the field itself looks like:

Label: 'Sort By: $(=vSortField)'

Expression : [$(=vSortField)]

As with everything in qlik, you'll have to play with the = and $() and 's to make it work the way you want it depending on what add-on you use to fill the variable.

Set the Sort By field as the top sort criterion in the sort section.

As a final step, add a Rows column that uses Rank and a reference to the [Sort By] field label:

Rank( TOTAL [Sort By: $(=vSortField)])

You'll end up with a sorted Sort By column which dupes one of the other columns, depending on the variable setting and a set of row numbers.

Good luck!

[AndrewMcIlwrick]

Thanks @rwunderlich - I was using striping in QS table with a MOD function and that also seems to break all sorting 😞