5 Replies Latest reply on Aug 22, 2013 12:45 AM by Leticia Soroa

# How can I sort a table and get TOP N by a field calculated in this way "LOOKUP(SUM([JTZ]), -1 ) - SUM([JTZ])" ?

Hi,

I am calculating the difference in exposure from two dates so I am using the formula LOOKUP(SUM([JTZ]), -1 ) - SUM([JTZ]).

This works perfectly fine however I would like to order my table by ASC or DESC. Currently I can do it in ascending order if I put my calculated value as first field in the rows section and hide the column (not ideal) .

How can I get top N? Index() does not seem to work in this case.

Thanks,

Leticia

• ###### 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])" ?

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])" ?

Hi Shawn,

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])" ?

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 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.

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])" ?

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])" ?

Thanks Jonathan!! Also i have been checking Drawing with Numbers and I have found some useful stuff!! Thanks!!