
1. Re: How can I sort a table and get TOP N by a field calculated in this way "LOOKUP(SUM([JTZ]), 1 )  SUM([JTZ])" ?
Shawn Wallwork Aug 16, 2013 11:53 AM (in response to Leticia Soroa)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"?
Shawn

2. Re: How can I sort a table and get TOP N by a field calculated in this way "LOOKUP(SUM([JTZ]), 1 )  SUM([JTZ])" ?
Leticia Soroa Aug 19, 2013 12:51 AM (in response to Shawn Wallwork)Hi Shawn,
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?
Thanks,
Leticia

3. Re: Re: How can I sort a table and get TOP N by a field calculated in this way "LOOKUP(SUM([JTZ]), 1 )  SUM([JTZ])" ?
Jonathan Drummey Aug 19, 2013 5:48 AM (in response to Leticia Soroa)Hi Leticia,
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 selfdata 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 rowlevel 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.
Jonathan
(Shawn, this would be a good one for TabWiki, remind me to write it up in a couple of weeks).

4. Re: Re: How can I sort a table and get TOP N by a field calculated in this way "LOOKUP(SUM([JTZ]), 1 )  SUM([JTZ])" ?
Leticia Soroa Aug 19, 2013 5:54 AM (in response to Jonathan Drummey)Thanks Jonathan! I will try this and let you know.
Thanks both for your help on this!

5. Re: Re: How can I sort a table and get TOP N by a field calculated in this way "LOOKUP(SUM([JTZ]), 1 )  SUM([JTZ])" ?
Leticia Soroa Aug 22, 2013 12:45 AM (in response to Leticia Soroa)Thanks Jonathan!! Also i have been checking Drawing with Numbers and I have found some useful stuff!! Thanks!!