topic Re: Sort by expression in load script and autonumber in QlikView

Sort by expression in load script and autonumber

Thu, 13 Jun 2013 13:27:43 GMT

Hello Community,

I've been playing around with this for awhile and I need help making it work (example QVW attached).

I have a table with the following fields: Date, Segment, State, Calls. My end goal is to have a table that shows the State and sum(Calls), then sorts the table Desc and make a new field that gives it a ranking (1, 2, 3, etc.). Is there a simple way to accomplish this?

Re: Sort by expression in load script and autonumber

Anonymous — Thu, 13 Jun 2013 13:38:34 GMT

There is a rank() command. If you enclose the expression you want to rank by within it you should be able to display the ranking.

rank(sum(Calls))

The sort order is set on the Sort properties of the chart, and you can remove the ability for users to change the sort order there as well.

Jonathan

Re: Sort by expression in load script and autonumber

Gysbert_Wassenaar — Thu, 13 Jun 2013 13:41:28 GMT

See attached example

Re: Sort by expression in load script and autonumber

Thu, 13 Jun 2013 14:14:11 GMT

Thank you! Is it possible to create a button that will change the chart to only show the top 10 ranked States?

Re: Sort by expression in load script and autonumber

Anonymous — Thu, 13 Jun 2013 14:25:27 GMT

You can set how many rows you want to show on the Dimension Limits properties. The button you speak of can be used to set a variable (Triggers>External>Set Variable) to 10. This variable can then be set as the limit on the Dimensions Limits properties.

Jonathan

Re: Sort by expression in load script and autonumber

Thu, 13 Jun 2013 14:30:26 GMT

Thanks, Jon. Problem is I am using a pivot table so there is not the option of dimension limits.

Re: Sort by expression in load script and autonumber

Anonymous — Thu, 13 Jun 2013 14:35:22 GMT

You could maybe test for a Rank(sum(Calls)) expession, only showing the result if the Rank is less than your variable. You could then set that variable to somthing very high (or a count of the dimension rows if you can work that out) and change it to 10 on your button push.

Jonathan

Re: Sort by expression in load script and autonumber

Thu, 13 Jun 2013 14:37:06 GMT

I'm afraid I do not follow, "test for a Rank(Sum(Calls)) expression"?

Re: Sort by expression in load script and autonumber

Anonymous — Thu, 13 Jun 2013 14:49:15 GMT

Something like this:

if(rank(sum(Calls))<=$(vReduce),sum(Calls))

Where vReduce is this variable that is either 10 or large enough to not reduce the rows in your privot table.

Jonathan

Re: Sort by expression in load script and autonumber

Thu, 13 Jun 2013 17:56:51 GMT

Beautiful, I got it to work perfectly. Thank you!