Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
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

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

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

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