10 Replies Latest reply on Aug 4, 2016 7:51 AM by Donna Coles

# How to show rank for filtered list while retaining rank of unfiltered list

Through various examples I've found online and seen demonstrated I know how to use table calcs to preserve the ranking of a set of items after they've been filtered, as demonstrated in this example by Joe Makohttp://public.tableau.com/profile/joe.mako#!/vizhome/FilterTrick_WithCalcjmedit/Dashboard

I have a need to extend this a bit further, and want to be able to show both the rank of the filtered list at the same time as preserving the overall rank.

eg in the example , if there were only 5 sub-categories displayed as all the rest had ben filtered out, you'd see a 'local' rank of 1-5 based on those 5 items, as well as the rank of where they sit in the overall list, so bookcases would always report an overall rank of 8 while it's 'local rank' would differ depend on what else had been filtered.

Is this possible (using v8.3.3)

• ###### 1. Re: How to show rank for filtered list while retaining rank of unfiltered list

I'll ponder this, for now the only thing I can think of is to calculate the first (unfiltered) rank outside of Tableau and then do the filtered rank inside of Tableau.

The issue is that table calcs are our only way to do arbitrary levels of ranking (yes, we can do some hacks using nested LOD calcs but they generate more and more subqueries and can only handle a specific number of ranks) and they are computed *after* dimension filters have been applied and *before* table calc filters are applied, so there's no way to rank/filter/rank. We can do filter/rank/filter, or filter/rank, rank/filter, but not rank/filter/rank.

Jonathan

1 of 1 people found this helpful
• ###### 2. Re: How to show rank for filtered list while retaining rank of unfiltered list

To me, a feature Tableau is missing is the ability to write a calculated field that can know what is selected in a quick filter, something akin to the ISMEMBER() function.

Although, as with anything in Tableau, our capabilities are bounded by the data source structure and contents, so if you can transform your data prior to Tableau, then here is one option:

You can multiply your data with a Cartesian join for the filter, and then you can get the interaction and result that you are looking for.

For example:

1. Connect to data source with the ability to use custom SQL (use Legacy connector for local files)

2. Use some form of SQL like:

SELECT * FROM [Orders\$],
(SELECT DISTINCT [Orders\$].[Product Sub-Category] AS [Product Sub-Category Filter]
FROM [Orders\$]) AS [Filter]

(The data will be duplicated by the number of distinct sub categories, and an extract is recommend. If you have a large number of underlying records, pre-aggregation may be an option)

3. Place pills for 'Product Sub-Category Filter' and 'Product Sub-Category' on the Rows shelf, and we get a mark for each combination, the original 17 marks has become 289 mark (17*17=289)

4. Place 'Product Sub-Category Filter' also on the Filter shelf. This will create the quick filter you will interact with

5. Place you Measure, in this case 'Sales', on the Columns shelf

6. Create a calculated field called 'Filtered Sales' like:

IF [Product Sub-Category]==[Product Sub-Category Filter] THEN [Sales] END

7. Sort the 'Product Sub-Category Filter' pill by 'Filtered Sales' and 'Product Sub-Category' by 'Sales'

8. Create a calculated field called 'Rank Filtered' like:

RANK_UNIQUE(SUM([Filtered Sales]),'desc')

and place it on the Rows shelf as a Discrete pill (or convert to Discrete in the Data Window), and set an Advanced compute using, addressing on both Dimensions 'Product Sub-Category Filter' and 'Product Sub-Category'

(This will rank the non-null values of Filtered Sales, returning Null when Filtered Sales is Null)

9. Right-click on one of the Null value headers and select Exclude, this is create a filter on that table calc pill.

(try filtering, and you will see the Filtered Rank adjust accordingly)

10. Create a calculated file called 'Rank' like:

RANK_UNIQUE(SUM([Sales]),'desc')

and place it on the Rows shelf as a Discrete pill (or convert to Discrete in the Data Window), and set the compute using to just 'Product Sub-Category' (no need for Advanced, as we want to partition by 'Product Sub-Category Filter')

11. Arrange your pills as you like, you are free to move 'Product Sub-Category Filter' or 'Product Sub-Category' to the Marks card so there is just one text header.

(interact with the filter and you will see the Filtered Rank changing, and Rank stays the same)

If you add another filter based on some other field, it will impact both ranks.

The attached performed these steps and was made in v8.3

Please let me know if you have any questions, or you are welcome to email me (found on my profile) and we can setup a screen share to go through this in more detail.

2 of 2 people found this helpful
• ###### 3. Re: How to show rank for filtered list while retaining rank of unfiltered list

Firstly - apologies for delayed reply - I've been on a training course.

Thanks very much for the info.  I'm pleased that what I was asking didn't turn out to be something simple that I was missing :-)

The data source I'm using is a pre built Tableau server extract used for other scenarios, so I'm limited with that structure for now.  My requirement isn't a business critical need - more of a curiosity.  I will have a play with the concept you shared anyway.

Many thanks to both you & Jonathan Drummey for you taking the time to respond.

• ###### 4. Re: How to show rank for filtered list while retaining rank of unfiltered list

I think Joe is right on the money with the IsMember() idea.  Although another way to do this would be if you could edit a set with the quick filter UI.  I know that people have wanted that as well.

Then, you could do this by doing the rank, and also doing the rank of SUM(IF INOUT([SetName]) THEN Sales ELSE NULL END)

this would give you the SUM of only things that are in the set.

:)ross

• ###### 5. Re: How to show rank for filtered list while retaining rank of unfiltered list

This post has been a huge help to me as I have a similar problem to this detailed in this thread Rank using Level of Detail calculation in v 9.0

I have a follow up issue - my original dataset has over 1 million rows and I have 150 categories to join so the multiplication is creating over 150 million rows!

The post above mentions that pre-aggregation may be an option - any suggestions as to how to approach this?

Many thanks

Howard

• ###### 6. Re: How to show rank for filtered list while retaining rank of unfiltered list

Here's the process I use:

1. Figure out what dimensions you need for filtering, blending, and as dimension pills in the view.

2. Then set up a an aggregate query that includes those dimensions in the GROUP BY of an aggregate query, with the measures given the appropriate aggregations.

3. Figure out what data you don't need and add that to the WHERE or HAVING clauses of the aggregate query to further reduce the records that the query has to process.

For example, if all you needed was those 150 categories * 12 months and no other dimensions then the aggregate query could be set up to return 150*12 = 1800 rows, then the cross product 1800*150 = 270K records in the Tableau data source, which is 99.8% smaller than 150M records.

Cheers,

Jonathan

• ###### 7. Re: How to show rank for filtered list while retaining rank of unfiltered list

Actually I dug up this forum post while looking for the same thing:

How do you show overall ranking after selecting x?

I was able to use it to filter and highlight.  The Rank and Size (X out of Y) works correctly.

And it works!  I had previously been resorting the user to using a parameter and quick filter but this works!

1 of 1 people found this helpful
• ###### 8. Re: How to show rank for filtered list while retaining rank of unfiltered list

Actually I got too happy.  I forgot Table calcs can be used in dashboard actions as a driver, but not a receiver.  I thought I had it because I was going to tie [Region Table Calc] to [RegionName] via an action, but I am unable to.

(I can get it to tie, but not with a single action)

• ###### 9. Re: How to show rank for filtered list while retaining rank of unfiltered list

Joe Mako et all,

I would humbly suggest that the missing feature is actually conditional hiding. Tableau has hiding, but it's manual, which means it's of no use for parameter driven content. Filtering and hiding would work exactly the same, except that hiding would happen downstream of final vis layer. You will see this capability in some html-oriented visualization tool (Logi Analytics comes to mind).

Having to do this upstream of Tableau in the DB is a loss for Tableau. All Tableau calcs can be done upstream in the database, but who wants' to do that!

• ###### 10. Re: How to show rank for filtered list while retaining rank of unfiltered list

Just closing the loop on this one - the advise offered in this thread helped me achieve what I was looking for.  https://community.tableau.com/message/516072?et=watches.email.thread#516072

Reading back on my original thread, I actually had a need to filter by an additional dimension so Pooja Gandhi response in the thread helped.  I realise this may be subtley different from what I described above... Thanks again for all the responses.

1 of 1 people found this helpful