Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sorting Problem for Straight Table

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

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

6 Replies
sujeetsingh
Master III
Master III

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

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor
Contributor

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
Luminary Alumni
Luminary Alumni

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