11 Replies Latest reply on Dec 19, 2011 1:45 PM by Shawn Wallwork

    Tableau Jedi Needed to Solve Sort Problem

    Robert Sinclair

      I need to Sort measurement Names dynamically based on avg measurement values that are selected based on filter actions.  I'm able to sort manually after an action but not dynamically.  I really feel that this is possible, but that I'm just not getting it.  I would really appreciate it if a Tableau Master could help me solve this one. 

       

      http://www.screencast.com/t/Q6q2k4FYVdq

       

      PS. I am connecting to a number of SQL tables, so the excel reshape tool is not an option.

        • 1. Re: Tableau Jedi Needed to Solve Sort Problem
          Arjan Eriks

          Hi R. Sinclair, can you add the workbook with data? I'll check for you. I am still online so probably can do it pretty fast. Rgds, Arjan

          • 2. Re: Tableau Jedi Needed to Solve Sort Problem
            Robert Sinclair

            Here is a sample worksheet that illustrates the problems.  I want the bars to sort dynamically in descending order each time a different filter is selected.

            • 3. Re: Tableau Jedi Needed to Solve Sort Problem
              Richard Leeke

              If you unpivot your data it's easy (see attached).  I don't know of any way to do it with Measure Names and Measure Values (I won't say you can't because Joe will just prove me wrong).

              • 4. Re: Tableau Jedi Needed to Solve Sort Problem
                Joe Mako

                This seems like a duplicate post of http://www.tableausoftware.com/support/forum/topic/sorting-measurement-values-dynamically and Alex offered the same option as Richard, so I am guessing reshaping your data is not going to work for your situation.

                 

                So here is an alternate solution, and depending on your exact requirements, there are a variety of options. Attached is the basic route to get what you are looking for with a data blend and a parameter.

                 

                The primary data source I use is this:

                 

                 

                Measure
                
                 Sport
                Health
                Hobby
                Profession
                Income
                


                 

                Then create a parameter from the Gender field, adding an "All" option, and create a calc field for each of your measures in your provided data source like:

                 

                Health Filtered:

                 

                IF [Gender]=[Gender Parameter] OR [Gender Parameter]="All" THEN [Health] END


                 

                Then in the new Primary data source, create a calc field for each (this may not be a necessary step in all cases):

                 

                Health:

                 

                AVG([TEST DATA].[Health Filtered])


                 

                Then add a calc field like:

                 

                Average Value:

                 

                CASE ATTR([Measure])
                
                 WHEN "Health" THEN [Health]
                WHEN "Hobby" THEN [Hobby]
                WHEN "Income" THEN [Income]
                WHEN "Profession" THEN [Profession]
                WHEN "Sport" THEN [Sport]
                END
                


                 

                Then for sorting, create a calc field like:

                 

                Sort Desc:

                 

                -[Average Value]


                 

                and layout the worksheet as attached. You will get a message telling you there is no relationship for the data blend, but you can dismiss this message.

                 

                So you have a choice, either reshape your data for Tableau, or jump through a few hoops inside of Tableau. If you want something more sophisticated there are other options and variations of what I have described here. If anything, when it comes to Tableau, you have options.

                • 5. Re: Tableau Jedi Needed to Solve Sort Problem
                  Alex Kerin

                  Great solution Joe. Odd in a way that I can sort the fields manually, but cannot do it automatically without these hoops

                  • 6. Re: Tableau Jedi Needed to Solve Sort Problem
                    Joe Mako

                    It is my guess that the Measure Names/Measure Values re-structure of data happens after the data is returned from the data source, and the Sort dialog on a field's value is sorting in the underlying data source. So in my view it is an order of operations situation.

                     

                    The choice is restructure before Tableau, and you can use the sort dialog on a field, or use a data blend like I have to enable the sort dialog on a field. Because the Measure Name concept happens after field value sorting, Measure Names is not enabled to be sorted on a field.

                    • 7. Re: Tableau Jedi Needed to Solve Sort Problem
                      Robert Sinclair

                      Thanks Joe!

                       

                      That does the trick! It's not the obvious fix I thought it might be ( i thought i was missing something simple) but it does achieve the results i was after. 

                       

                      Very much appreciated,

                      RS

                      • 8. Re: Tableau Jedi Needed to Solve Sort Problem
                        Robert Sinclair

                        Joe,

                         

                        I was a bit too quick in thinking that my sort problem was solved.  What modification would I have to make to your proposed solution, so that it would work when another worksheet is acting as a filter?

                         

                        http://screencast.com/t/xujBMzwG

                         

                        Thanks

                        RS

                        • 9. Re: Tableau Jedi Needed to Solve Sort Problem
                          Joe Mako

                          That is doable as well. you would need to generate a primary data source with one row for every possible combination of zip code and measure, like

                           

                           

                          Zip    Measure
                          
                           12345    Sport
                          12345    Health
                          12345    Hobby
                          12345    Profession
                          12345    Income
                          78945    Sport
                          78945    Health
                          78945    Hobby
                          78945    Profession
                          78945    Income
                          45612    Sport
                          45612    Health
                          45612    Hobby
                          45612    Profession
                          45612    Income
                          


                           

                          for 3 zip codes.

                           

                          Then your relationship for the data blend will be on the zip code field.

                           

                          Because you want an average for the values for all zip codes selected, we will have to perform the average later, so each of the fields that brings the measure across the blend will need to change from AVG to SUM, and we need another field setup like the others for Count

                           

                          Finally you will need a table calc for the Average Value like:

                           

                           

                          IF FIRST()==0 THEN
                          
                           CASE ATTR([Measure])
                          WHEN "Health" THEN WINDOW_SUM([Health],0,IIF(FIRST()==0,LAST(),0))
                          WHEN "Hobby" THEN WINDOW_SUM([Hobby],0,IIF(FIRST()==0,LAST(),0))
                          WHEN "Income" THEN WINDOW_SUM([Income],0,IIF(FIRST()==0,LAST(),0))
                          WHEN "Profession" THEN WINDOW_SUM([Profession],0,IIF(FIRST()==0,LAST(),0))
                          WHEN "Sport" THEN WINDOW_SUM([Sport],0,IIF(FIRST()==0,LAST(),0))
                          END / WINDOW_SUM([Count],0,IIF(FIRST()==0,LAST(),0))
                          END
                          


                           

                          it may look gnarly, but is is just a window sum for each measure with a divide by window sum of count at the end, all wrapped in an iff statement to show one mark. When this pill is places on the worksheet, you will want to use the Zip code field for the Compute using setting.

                           

                          Then you setup your action, using the Selected fields option, and choosing the new data source of Measure/zip combos as the destination data source.

                           

                          and that is about it.

                           

                          I did this in the attached.

                          • 10. Re: Tableau Jedi Needed to Solve Sort Problem
                            Robert Sinclair

                            You are truly a Tableau Jedi.  Thanks Joe

                            • 11. Re: Tableau Jedi Needed to Solve Sort Problem
                              Shawn Wallwork

                              Hi Folks, I've read several different threads dealing with sort. It seems there are a couple of different solutions when it comes to dynamic sorts on multiple fields. Before I start down one of these rabbit holes, I could use some advice on which route to take.

                               

                              The attached workbook contains a simplified version of a typical set of dashboards our company uses for media planning, in this case its a radio study. The sheet I'm interested in sorting is STLCH Radio Dash. While I don't need to sort the Day Part Study Dash sheet, it does show how the complexities can multiply fairly quickly. No matter what we deliver to the planners it seems they are always asking if we can add another dimension or measure, so the numbers of parameters will continue to increase and change over time. (There are endless categories when it comes to slicing and dicing audience/listeners, especially when you start adding in psycho-graphics.)

                               

                              So I guess the question is what is the most scale-able and adaptable approach to take given this type of data?

                               

                              Thanks --Shawn