7 Replies Latest reply on Apr 2, 2014 2:56 AM by Siraj Samsudeen

    Sorting different fields on same view/set

    Carlos Albuquerque

      I am trying to sort my visualization based on two different fields. The members that are in the set should be sorted based on the highest total, on the other hand, the members that are OUT of the set should be sorted based on a specific region, for example Central. Is that something possible to accomplish? My workbook is attached.

       

      The image below shows the first step complete which is the entire sheet to be sorted based on totals, however, i would like the members of the set that fall under the "Others" category to be sorted first by region, and then by total. So the reps that belong to Central would show first, regardless if there are other reps from different regions with higher totals...

      test.png

        • 1. Re: Sorting different fields on same view/set
          Siraj Samsudeen

          As far as I know, it is not possible to achieve what you want directly in Tableau as the sort settings are uniformly applied to the entire view. However, there is another "out-of-the-box" way to achieve what you want. Instead of creating this as one view/worksheets, create 2 worksheets and then combine them into as dashboard. Then, you can have 2 different sorting criteria applied to the the 2 views in the worksheets. However, this is more work for you as you have to duplicate the worksheet and apply a filter only to show the IN values in one sheet and OUT values in another sheet. Please let me know if this would work for you.

          1 of 1 people found this helpful
          • 2. Re: Sorting different fields on same view/set
            Carlos Albuquerque

            Hi Siraj,

             

            Thanks for the response. The extra work will not be a problem, but I am not sure it will work for my example. I have a parameter that lets the user define how many rows he wants to see as the "Top Rows". So when a selection is made, the "Others" field either expand or collapse as the Top selection changed. With two different sheets, how would i be able to leverage that top row parameter and apply to the second sheet at the same time? Does that make sense?

            • 3. Re: Sorting different fields on same view/set
              Jonathan Drummey

              Hi Carlos,

               

              How's this:

               

              2014-03-31 10_26_13-Tableau - test.png

               

              Here's what I did:

               

              1) Changed Subset Labels to be an measure using the ATTR() aggregation. This isn't strictly necessary for the view, I find it helpful to have as few dimensions in the view when using table calculations.

               

              2) Created a Sort calculated field with the formula:

               

              IF MIN(IF [Top N Reps] THEN 1 END) == 1 THEN

                  -SUM([Total])

              ELSE

                  -WINDOW_SUM(SUM([Total]))

              END

               

              This will have a Compute Using on the Rep, so it partitions for each combination of values of Region and Set. This returns a set of numbers that Tableau's default sort for measures will return in the proper order.

               

              3) Put the Sort calculated field as a discrete (blue) pill in the view prior to the Rep, so the reps are now sorted.

               

              Jonathan

              • 4. Re: Sorting different fields on same view/set
                Carlos Albuquerque

                Jonathan,

                 

                This is awesome!! Worked like a charm. Thank you very much!

                If its not much to ask, would you be able to go over the sort calculated field? I am not sure i understand the entire logic behind it in combination with the way tableau sorted the field...

                • 5. Re: Sorting different fields on same view/set
                  Siraj Samsudeen

                  Jonathan, this is brilliant! As Carlos has asked for more explanation, I would also ask a little bit more explanation as to how the sort field works.

                   

                  I was thinking it would be nice if Tableau allowed a formula in the sort dialog box as it allows in the filter dialog box. But you have provided a clever workaround using calculated field - I need to master table calculations a little more to fully grasp your answer. Thanks a lot Jonathan!

                  • 6. Re: Sorting different fields on same view/set
                    Jonathan Drummey

                    Thanks for the appreciation and the compliments...here's a more detailed explanation.

                     

                    A regular dimension pill can be sorted manually, alphanumerically, or via an aggregate measure. Aggregate pills and table calcs only have two options: alphanumeric or manual. I set up the measure to return the negative sum of total because Tableau's alphanumeric sort is an ascending sort, that way bigger values will be turned into bigger negative numbers and therefore be earlier in the sort. We can see this if I turn off Show Header for the Sort pill:

                     

                    2014-04-01 09_57_26-Tableau - sort with table calc.png

                     

                    Now about how I set up the calculation. First of all, we need to start with the level of granularity of the view. There are three dimensions in the view, the IN/OUT of the Set, The Rep, and the Region. So the SUM(Total) is being summed up for each distinct combination the values of those dimensions, making the bars. To get the desired sort, we need to return the SUM(Total) for each distinct Rep&Region in the Top N Reps of the Set, and return the SUM(Total) across all Reps for each Region in Other of the Set.

                     

                    When we want to have a calculation that goes across a set of marks in the view, that calls for table calculations, a data blend, or custom SQL/custom query/view. In this case I chose a table calculation, here once again is the formula for the Sort field:

                     

                    IF MIN(IF [Top N Reps] THEN 1 END) == 1 THEN

                        -SUM([Total])

                    ELSE

                        -WINDOW_SUM(SUM([Total]))

                    END

                     

                    Because the result of the field is an aggregate, the IF statement has to be evaluating an aggregate. Tableau does this so that the user intention is clear. Now, the [Top N Reps] set evaluates to a boolean True/False, and Tableau won't let us just do a MIN([Top N Reps]). So I did a row-level IF [Top N Reps] THEN 1 END inside the MIN to get around that.This calc only returns 1 for those records that are in the Set, and Null for everything else. Therefore, the operation is "If (for the level of granularity of the view) the MIN of (IF [Top N Reps THEN 1 END) is 1 then return the -SUM(Total) (for the level of granulairty of the view)". This what gets us the -3109 and -3102 for the first two rows in the screenshot.

                     

                    Now for the ELSE statement, which will be used for the Others part of the Set. The goal for these rows to return the SUM(Total) for all Reps for each Region. Since the Rep is in the view, that means we need to aggregate those SUMs and I chose to use the WINDOW_SUM() table calculation. This calculation sums up the aggregate measure across all of the addresses aka rows in the partition for each partition. The Compute Using (addressing) of the table calculation is set to be on Rep, so the calculation partitions (restarts) on each new combination of Region & Set. So, what that calculation does is that for each Rep, it sums up the SUM(Total) for all Reps within that particular Rep's Region & Set. This then returns the same result for all Reps in each Region & Set, and can then be used as a sort field.


                    One optimization that I often use in cases like this is to wrap the WINDOW_SUM() in a PREVIOUS_VALUE, like this:


                    -PREVIOUS_VALUE(WINDOW_SUM(SUM([Total])))


                    PREVIOUS_VALUE is Tableau's only real iterative calculation, it returns the result of this table calculation from the previous address aka row in the partition to the current address, and takes as an argument a value for the first address (Rep) in the partition. In the original calc, the WINDOW_SUM() is re-computed for every address (i.e. every Rep). If you're computing only a dozen WINDOW_SUMs, that won't be noticeable. However, if you're computing 50K or 500K WINDOW_SUMs, the performance hit will be quite noticeable. Using PREVIOUS_VALUE() causes the inner calc to only be evaluated once for each partition, and just returns that result to every other address (Rep) in the partition.


                    Table calculations are wonderfully powerful and not the easiest thing to learn, here's a page with links to explanatory pages:  http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/


                    Let me know if you have any questions!


                    Jonathan

                    • 7. Re: Sorting different fields on same view/set
                      Siraj Samsudeen

                      Wow Jonathan! Thank you so much for taking your time to write such a long post! I feel so grateful to see such willing help in this forum. I will read and digest it and post here if I have follow-up questions.

                       

                      It is almost as if you have read my mind. I was thinking of posting in the forum asking for advice on the various links to master table calculations as this is an area I really want to master. I attended the Tableau advanced classes in Geneva with high expectations that I can really grasp table calculations, but the class turned out to be quite a disappointment as the instructor himself did not understand table calculations well. None of the books really cover it deeply. So, your collection of links has provided me food for weeks, if not months. Thank you so much again and have a nice day!