Sort by expression in load script and autonumber

[#1]

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?

[Anonymous]

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

[Gysbert_Wassenaar · Accepted solution]

See attached example

[#4]

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

[Anonymous]

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

[#6]

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

[Anonymous]

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

[#8]

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

[Anonymous]

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

[#10]

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