
1. Re: sorting on difference from average
Tracy Rodgers May 14, 2012 12:13 PM (in response to Andrew Marritt)1 of 1 people found this helpfulHi Andrew,
Is the calculation a table calculation? If it is, it is not currently possible to sort by table calculations, as this might alter the data in some cases.
Tracy

2. Re: sorting on difference from average
Andrew Marritt May 14, 2012 1:13 PM (in response to Tracy Rodgers)Yes, it's a table calculation. I guess this means that I have to do something outside Tableau.

3. Re: sorting on difference from average
Jonathan Drummey May 15, 2012 7:00 AM (in response to Andrew Marritt)Hi Andrew,
The sorts that you set from the data window or by clicking on a pill and choosing Sort from the context menu are used by Tableau in the queries that it generates against the datasource. Table calculations are performed by Tableau on the data that it retrieves, and can be highly dependent on sort order themselves so it makes sense that they are not availabe in the context menu. However, like most things in Tableau, there's a workaround:
1. Create a version of your table calculation that will provide correct results for Tableau's default alphanumeric sorting method. For example, if you have want to sort by the largest positive difference down to the largest negative difference, then you'd use [Difference] to make the largest positive difference have the smallest number.
2. Rightclick on the calculation in the Measures Shelf and choose Convert to Discrete. For numeric measures, this changes the green =# icon into a blue one. (Alternatively, you can leave the table calculation as is and then convert it to discrete after you've dragged it into the view in the next step).
3. Drag that calc onto the Shelf that needs to be sorted to the left of the dimension that is to be sorted. The view will now be sorted.
4. Click on the blue calculation pill and turn off Show Header.
One extra note since I was just dealing with this yesterday: When you turn off Show Header, it turns off Show Header for all instances of that pill in the view, so if you need a calculation for sorting and then want to display it elsewhere in the view as a header, then you'll need to have two calculations, the simplest way is to have one calc that does the real calculation, and then the second that just has [First Calc]. That way you can set the Compute Using for the first calc and get that working right, then drag the second calc into the view to use for the sort.
Cheers,
Jonathan

4. Re: sorting on difference from average
Andrew Marritt May 15, 2012 7:20 AM (in response to Andrew Marritt)Jonathan. That is brilliant, thanks a massive amount.
One thing I found, given the sorting is by alphabetic order was that the data went from 1 >0 then 1 > 0 rather than a continuous largest to smallest (the range was 1 > 1 shown as percentages). Adding 1 to the calculation (shifting the range from 2 > 0 sorted this and as I had a calculation just for the sorting I didn't need to show this.
Again, wonderful, wonderful help.

5. Re: sorting on difference from average
Jonathan Drummey May 15, 2012 8:05 AM (in response to Andrew Marritt)I'm curious, I'm trying to duplicate what you are seeing and haven't been able to using a table calc, thouch I can with a nontable calc and string data. I've always seen Tableau sort from the largest negative number to the largest positive number for numeric data. If you have the opportunity to post some screenshots or packaged workbook, I'd like to get a better understanding of what you are seeing.
Thanks,
Jonathan

6. Re: sorting on difference from average
Andrew Marritt May 15, 2012 8:27 AM (in response to Jonathan Drummey)Jonathan,
I've just tried to replicate the issue and have not been able to. If I spot it again I'll provide a screenshot.
Andrew

7. Re: sorting on difference from average
Jonathan Drummey May 15, 2012 9:20 AM (in response to Andrew Marritt)Ok, thanks!