6 Replies Latest reply on Feb 12, 2014 10:47 AM by Ben Shirley

    Sort/Keep Top X by Calculated Field Problem

    Nathan Schofield

      Hi All,

       

      I have a Visualisation for which I want to be able to sort/Keep Top X by a calculated field. When I try to do either of these the calculated field does not show in the list.

       

      I have attached an example using dummy data.

       

      The first tab shows the basic structure of the data.

       

      The second tab is the visualisation I want to achieve. I would like to dynamically keep the Top X countries by the calculated field 'Rate of Change (absolute)'.

       

      If I right click on the 'country' pill and click sort, I cannot select the 'Rate of Change...' to sort by. Similarly, if I drag 'Country' into the 'Filters' box I click on 'Top' and 'by field' there is no 'Rate of Change...' option.

       

       

      Please help me understand why I can't achieve this.

       

      Regards,

       

      Nathan Schofield

        • 1. Re: Sort/Keep Top X by Calculated Field Problem
          Robin Kennedy

          Nathan,

           

          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.

          Source: http://kb.tableausoftware.com/articles/knowledgebase/sorting-concepts-related-quick-table-calculations

           

          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!

           

          Robin.

          • 2. Re: Sort/Keep Top X by Calculated Field Problem
            Jonathan Drummey

            Hi Robin,

             

            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.

             

            Jonathan

            • 3. Re: Sort/Keep Top X by Calculated Field Problem
              Robin Kennedy

              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.

               

              R.

              • 4. Re: Sort/Keep Top X by Calculated Field Problem
                Jonathan Drummey

                "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!

                • 5. Re: Sort/Keep Top X by Calculated Field Problem
                  Nathan Schofield

                  Hi Robin and Jonathan,

                   

                  I have not had time to test and implement, but wanted to let you both know I appreciate your input!

                   

                  Nathan

                  • 6. Re: Sort/Keep Top X by Calculated Field Problem
                    Ben Shirley

                    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:

                    Filtering Rows in Subtables from Access

                     

                     

                    Thanks

                    Ben