7 Replies Latest reply on May 15, 2012 9:20 AM by Jonathan Drummey

    sorting on difference from average

    Andrew Marritt

      I'm analysing survey data with 5 different groups. (say groups A...E)


      The data is in the format ID, Group, Question, Answer.  Questions are all Likert-type with negative / neutral / positive / NA


      I use a net rate calculation for each question ([#Positive responses]-[#Negative responses])/[#responses] shown as an average for each question


      For a selected group (selected using a parameter) I find the difference from the average excluding that group:


      [net rate selected group]-[net rate all other groups]


      This all seems fine & working


      I now want to sort on this final calculation - the difference so I can show the questions for the selected group with the biggest difference from the rest of the population.  When I use the 'Sort' and try and sort by field my final calculation isn't being shown as an option.



        • 1. Re: sorting on difference from average
          Tracy Rodgers

          Hi 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.



          1 of 1 people found this helpful
          • 2. Re: sorting on difference from average
            Andrew Marritt

            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

              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. Right-click 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.





              • 4. Re: sorting on difference from average
                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

                  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 non-table 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.





                  • 6. Re: sorting on difference from average
                    Andrew Marritt



                    I've just tried to replicate the issue and have not been able to.  If I spot it again I'll provide a screenshot.