9 Replies Latest reply on Sep 5, 2016 5:07 PM by Alper Akbas

    How can I automatically sort a bar chart with measure names/values?

    Louis Keating

      Hey all,

       

      I have created a bar chart with 12 measure names and measure values (picture below). It also contains a calculated field which is based on a parameter. The parameter is a drop down which allows you to select a subset of the total values.

       

      delete.png

       

      When I click the sort descending button in the worksheet view, it gives me the desired result. However, if I change my parameter to a different value, the bars keep the same order. Clicking the descending button again sorts it out but how can I automate this?

       

      You'll notice on the measure names the little sort button. I've tried using this but it doesn't give the option to sort by a field - only alphabetically.

       

      I've also tried without the parameter/calculated field and I have the same problem.

       

      Does anyone have any ideas?

       

      Cheers,

      Lucas

        • 1. Re: How can I automatically sort a bar chart with measure names/values?
          vishwanath Pendyala

          Hi Louis

           

          I remember working on a similar issue recently, I am attaching that workbook . Let me know if it helps or if you have any questions.

           

          Thanks

          Karthik

          1 of 1 people found this helpful
          • 2. Re: How can I automatically sort a bar chart with measure names/values?
            Louis Keating

            Hey Vishwanath,

             

            I like what you did with the ascending and descending metric but unfortunately my problem is more simplistic. I ALWAYS want it to sort descending, but I want it to update automatically when i change the filter/parameter.

             

            Thanks for your help,

            Lucas

            • 3. Re: How can I automatically sort a bar chart with measure names/values?
              Jim Wahl

              Hi Louis,

               

              I'm not sure if you can force a sort order with measure names / measure values pills. When Tableau's Sort dialog doesn't work or when you want to force a sort order, the normal approach is to create a discrete calculated field with the value you want to sort on and place this to the left of the dimension you want to sort by in on the Rows or Columns shelf.

               

              But this doesn't work with Mesure Names / Measure Values, because you can't reference Measure Names/Measure Values in a calculated field.

               

              In general, you might be better off with reshaped data, since it seems like each measure represents a dimension. For example, instead of having separate measures for Mens_high_fashion_spend, Mensware_spend, ... you'd have a single Spend measure and a Category dimension for Mens_high_fashion, Mensware, ...

               

              Your data will go from a wide format with columns for each spend category to a tall format with a column for Category and Spend (and whatever other columns are required, such as Date, ...).

               

              You can reshape using a custom SQL connection or Tableau's Excel plugin.

               

              With your data in this format, you'll be given the option in the Sort dialog to sort by the sum of sales descending.

               

              If you can't go this route, there is another path, but I expect reshaping is going to make your life easier and enable new calculations.

               

              Jim

              3 of 3 people found this helpful
              • 4. Re: How can I automatically sort a bar chart with measure names/values?
                Louis Keating

                Hey Jim,

                 

                Yea I thought that might be the solution. Cheers for all the explanation!

                 

                However, you've piqued my interest... What is this other, less trodden path?

                 

                Cheers,

                Lucas

                • 5. Re: How can I automatically sort a bar chart with measure names/values?
                  Jim Wahl

                  The workaround---that first comes to mind, I wouldn't be surprised if there are others---is to use a "data scaffold" and blending.

                   

                  This isn't as hard as it might sound, but it comes with some maintenance overhead.

                   

                  For example, in the SuperStore data set, here is a view that is similar to your initial view.

                  2014-04-09_10-31-05.png

                   

                  As you said, I can't click on Measure Names and select a sort order based on the value. All I can do is sort manually apply a descending sort order.

                   

                  The workaround is to create a scaffold with rows for every combination of dimensions you have in the view. At a minimum this is Measure Names 2 and a dummy linking field I called Link and set to 1:

                  Measure Names 2Link
                  Sales1
                  Profit1
                  Order Quantity1
                  Shipping Cost1

                   

                  But if you want to filter by Year, you'd also need a Year column and rows for each year, which will link to the main data source.

                     

                  YearMeasure Names 2Link
                  2010Sales1
                  2010Profit1
                  2010Order Quantity1
                  2010Shipping Cost1
                  2011Sales1
                  2011Profit1
                  2011Order Quantity1
                  2011Shipping Cost1
                  2012Sales1
                  2012Profit1
                  2012Order Quantity1
                  2012Shipping Cost1
                  2013Sales1
                  2013Profit1
                  2013Order Quantity1
                  2013Shipping Cost1

                   

                  You can build this in Excel or just cut-and-paste it into Tableau to create the scaffold data source. (Tableau's reshape tool comes in handy when you have multiple dimensions.)

                   

                  In this scaffold data source, you can create a calculated field called Value, which will pull the data from the main (but in your worksheet, secondary) data source.

                  Value =

                  CASE MIN([Measure Names 2])
                  WHEN 'Sales' THEN SUM([Sample - Superstore Subset (Excel)].[Sales])
                  WHEN 'Profit' THEN SUM([Sample - Superstore Subset (Excel)].[Profit])
                  WHEN 'Order Quantity' THEN SUM([Sample - Superstore Subset (Excel)].[Order Quantity ])
                  WHEN 'Shipping Cost' THEN SUM([Sample - Superstore Subset (Excel)].[Shipping Cost])
                  END
                  

                   

                  The MIN() function is required around Measure Names 2, because all of the other fields are aggregates (you can only blend aggregates). Since the worksheet is at the Measure Names 2 level of detail (i.e, there is one row for each value), MIN()==MAX()==ATTR(), I just chose MIN().

                   

                  You can also create a Sort Order field based on Value. When you add a pill to the left of the Measure Names 2 dimension, Tableau will sort this ascending, so I created a Sort Order pill that is just -[Value] to make the order descending.

                   

                  After adding Sort Order to the view, you can right-click the pill and deselect Show Header to hide this column.

                  2014-04-09_10-58-56.png

                   

                  This isn't ideal. Again, I think you might be better off reshaping your data. It'll make things like this easier---Tableau works better with tall data.

                   

                  Jim

                  2 of 2 people found this helpful
                  • 6. Re: How can I automatically sort a bar chart with measure names/values?
                    ericbelen0

                    Thread resurrected!

                     

                    Thank you Jim - works like a charm. Question - is it possible for the index to not count the hidden rows? See image below

                     

                    rows.JPG

                    Edit: figured out a work around/manual way - if I do the clean up first then apply the index it will self correct. If in case somebody can figure a dynamic way of doing it please update

                    • 7. Re: How can I automatically sort a bar chart with measure names/values?
                      Jim Wahl

                      Probably -- Its a little difficult for me to tell how you've hidden the rows. Did you use a table calc filter or a regular filter?

                       

                      If the latter, you should be able to adjust the addressing / partitioning on the index formula to get a sequential list.

                       

                      Perhaps you could mock up a workbook and post a twbx. Or we could do a short screen share.

                       

                      Jim

                      • 8. Re: How can I automatically sort a bar chart with measure names/values?
                        Justin Price

                        I found a very easy solution that seems to work consistently for me. In the row and/or columns bar click the down arrow on the dimension (blue pill) and change the sort to "Sort By = Field ...".

                        5 of 5 people found this helpful