Your calculated field is a Table Calculation and it is not possible to use one of these fields in a sort dialogue box because table calcs occur after the ordinary calculations and filtering and sorting has happened.
Tableau's own explanation is as follows:
The Sort dialog box never displays or includes Quick Table Calculations. This is because Tableau Quick Table Calculations occur as a post-database processing step, and Tableau does not go back to the underlying data to perform a second pass through the data simply to sort the Tableau Quick Table Calculations results.
Having said that, there is a work around to get a sort working. You need to add the table calc as a discrete field to your columns and turn off all other sorts. This way, Tableau's default sorting applies -- in this case in number order from lowest to highest. To get highest to lowest you just put a negative sign in front of the formula.
I'm stumped on how to get a work around for the Top X calculation though. It might need some Wallwork wizardry or some Mako magic. I tried to use a filter on Rank, but this doesn't work properly because the Rank is evaluated at the same time as the other Table calcs, including the sort as described above. The result is the Rank is computed before the sort is applied and so alphabetically, not by the max to min of the measure.
I have attached my work here. Hope it helps somewhat!
Sort by Calc Field.twbx.zip 13.0 KB
Table calcs are awesome, and sometimes because of their limits - performance, limited sorting of results, can't address on aggregates, can't address or partition by results of table calcs being the big four issues in my mind - it's better to use other means of getting the job done.
Here's an alternative that gets the sort. The "trick" here is to take the Month out of the view and not use table calcs. A second parameter is added for the comparison month, and all the calcs can be done inside regular aggregates, which enables us to get the desired sort for Country that can let a Top N filter work, and also at that point you can use a regular Top N filter on Country and not need the table calc filter.
The one downside here is needing to update two parameters for each new month's worth of data instead of one, however if there's a lot of data this view will likely be faster than table calcs.
I've got a hazy concept of how to get the desired result in table calcs using one or more sets and nested table calcs, but I think the technique I proposed is a lot more manageable.
Hey Johnathan, thanks for your feedback there...
I did start playing around with sets and the like to get the filter working but it made my head hurt.
Your technique is much more elegant and easy to implement; I like it! Hopefully it's useful for the OP and he wasn't over-simplifying the workbook for the purposes of this example.
"I did start playing around with sets and the like to get the filter working but it made my head hurt."
That's why I didn't go there!
Hi Robin and Jonathan,
I have not had time to test and implement, but wanted to let you both know I appreciate your input!
Is there a way to reverse this so it shows the last 3 records rather than the first 3 records? also the number of records varies so it can just be look at record 5 4 and 3. ?
This is what i am trying to sort out: