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.
sort dynamic karthik.twbx 131.7 KB
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,
3 of 3 people found this helpful
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.
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?
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.
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 2 Link Sales 1 Profit 1 Order Quantity 1 Shipping Cost 1
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.
Year Measure Names 2 Link 2010 Sales 1 2010 Profit 1 2010 Order Quantity 1 2010 Shipping Cost 1 2011 Sales 1 2011 Profit 1 2011 Order Quantity 1 2011 Shipping Cost 1 2012 Sales 1 2012 Profit 1 2012 Order Quantity 1 2012 Shipping Cost 1 2013 Sales 1 2013 Profit 1 2013 Order Quantity 1 2013 Shipping Cost 1
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.
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.
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.
ScaffoldEx.twbx 1.4 MB
Thank you Jim - works like a charm. Question - is it possible for the index to not count the hidden rows? See image below
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
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.
6 of 6 people found this helpful
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 ...".