5 Replies Latest reply on Aug 11, 2016 3:12 AM by Simon Runc

# How to sort a parameter selection to the top

Hi friends!  I'm having fun trying (for the first time) to complete today's Data School Gym challenge (http://www.vizwiz.com/2016/08/the-data-school-gym-sorting-dimension.html) and I've got the first 5 of 6 requirements done, but I am totally stuck on how to move a parameter selection to the top of my list! I imagine it's got something to do with creating a calculation that modifies rank based on the parameter selection, then sorting by that calculation but I'm not skilled enough at writing calculations yet to know where to start. Any help would be appreciated!

• ###### 1. Re: How to sort a parameter selection to the top

hi Vincent,

Well as long as you credit me when you post your solution!!!....

So there are many ways to do this...one way is this calculation to use as the sort by (I've mocked up a similar situation using superstore)

[Sort on Parameter Selection]

IF [Select City] = 'All' THEN SUM([Sales])

ELSE SUM(IIF([Select City] = [City],1000000000,[Sales]))

END

Hopefully you can see what it's doing, but please post back if not and I can explain.

• ###### 2. Re: How to sort a parameter selection to the top

This is very close to what I'm looking for, Simon. The problem is that I've currently got it sorted by descending maximum for a particular field. I'd like to keep it this way, except that the chosen country is brought up to the very top of the list. The calculation you gave, if I understand it, does the following:

If the user selects All, display as usual.

If the user selects a particular country, return a value of 1 billion

Since the measures I'm using don't go nearly that high, 1 billion will always be at the top of my list (since I'm sorting it descending). How could we maintain a secondary sort though? Essentially, I want to sort first by this calculation, then by descending maximum.

Thanks a bunch for your help, Simon!

• ###### 3. Re: How to sort a parameter selection to the top

Yes spot on with how it works...so another way (and probably simpler) is to take advantage of the fact Tableau sorts Blue Pills in ascending order...

So I've amended my formula to be

[Sort on Parameter Selection Opt 2]

IF [Select City] = 'All' THEN 0

ELSE IIF([Select City] = [City],-1,0)

END

Notice we no longer have any aggregations (so no SUM) which means I can use this as a dimension. I then bring it in to the left of City...and if all selected the default sort is maintained, but if a city is selected that city takes on the value -1 and so is brought to the top (I've left it un-hidden so you can see what it does, but can untick show header in your final version.

Always lots of ways to do things in Tableau

• ###### 4. Re: How to sort a parameter selection to the top

This is awesome! Thanks, Simon!

I was never really clear on when and how Tableau is able to convert between continuous and discreet, but this clears it up quite a bit. So essentially, this calculation is saying "If the user selects a country, give it a value of -1. Otherwise leave it as is." That way, automatic sorting will bring any selected country to the top row. Nice and simple! I'll be sure to throw some credit your way, too.

• ###### 5. Re: How to sort a parameter selection to the top

No problem...

On discrete/continuous (or blue/green pills). In Tableau we can use (well apart from logical restrictions...eg. a string can't be continuous) any measure or dimension as a discrete (blue) or continuous (green) pill (we can change this by clicking on the pill) and we can even use the same measure/dim in the same Viz multiple times (using it as blue and green). Here's my fave article on the subject

Blue things and Green things - The Information Lab

in short, Blue pills create headers and Green pills axis (and that's all you really need to know!)...so for example, if you created a field [Sales Sorter] which is SUM([Sales])*-1, make this discrete and bring it in to the left of the city (in my example) this would force a descending sort on sales...I tend to prefer this to using the sort option as it gives me more control (eg. create a parameter [Sort by], with 2 option "Sort by Sales", "Sort by Quantity"...create a field IF [Sort By] =   "Sort by Sales" THEN SUM([Sales])*-1 ELSE SUM([Quantity])*-1 END. Make this blue, bring it to the left of city, and the parameter will now control how it's sorted)