Sorting on a field or combination of fields and then hiding them is a very common way to get the sort you're looking for. So why doesn't this work for you situation? What isn't "ideal"?
Thanks for your reply.
This situation is not ideal because I can only sort the rows in ascending order. Any idea how to do it descending?
Also can I really get top 10? how does the index work in this cases?
Because the LOOKUP(SUM([JTZ]), -1 ) - SUM([JTZ]) is a table calculation, our ability to use those results in other areas of Tableau (like sorting dimensions) is limited.
The workaround for sorting by a table calculation result in descending order is to create a calculated field that has the formula -[your table calc], make it discrete, put it on the Rows or Columns Shelf to the left of your desired view, and then uncheck Show Headers for that pill. I set that up in the "Sort by TC Measure Descending" in the attached.
However, that's just sorting the visual display. To do a top N filter we'd want to sort the dimension by that table calculation result for the top N calculation, and Tableau won't let us do that kind of sort. There are two workarounds I know of:
- Set up a self-data blend, where the blend is on an altered dimension (like date) that sets up the secondary to return teh prior value. Then we can use regular aggregates to get the difference. We can't sort a dimension in the primary by a value that is derived from the secondary, but we can get there by creating a sort field as above and then using an INDEX() calculation with a relative compute using (such as Table (Down), etc.) to get the Top N.
- Use a custom query to do the work of joining the data at the appropriate level so the prior value is an additional column. At that point, the calculations can be row-level or simple aggregates and we can use all of Tableau's sort functionality and even a Top N filter.
I set both of these up in the attached.
(Shawn, this would be a good one for TabWiki, remind me to write it up in a couple of weeks).
Thanks Jonathan! I will try this and let you know.
Thanks both for your help on this!
Thanks Jonathan!! Also i have been checking Drawing with Numbers and I have found some useful stuff!! Thanks!!