12 Replies Latest reply on Aug 19, 2016 2:15 AM by Rudranshu Praharaj

# Sorting/Filtering on Dimension and Sub-Dimension

Hi all,

Please find attached some test data (just 3 columns...) and worksheet to help explain my problem.

 Account Sub Account Value A 1 30000 A 2 30000 ... (see attached) ...

I am trying to

• sort the accounts by sum of value of all their sub-accounts (easy - even I managed that)
• sort the sub-accounts of an account by their value (it's not doing what i want - presumably because many accounts have a sub-account with the same name; however there should be no connection between e.g. A-1 and B-1)

Once that's done, I want to

• show only the top 3 accounts by sum of value of all their sub-accounts (I got this to work until I added sub-accounts)
• show only the top 3 sub-accounts by value for every account

All this should probably be quite straightforward - can someone please help? :-)

Thank you!

• ###### 1. Re: Sorting/Filtering on Dimension and Sub-Dimension

You can sort the sub accounts by creating a combined field from account and sub account. That way it'll treat sub accounts with the same name differently, since they have different accounts. Then you can bring it into the view, sort using it, and hide the header. To keep the top 3 of the sub accounts, I brought in your rank calculation, set it to calculate using sub account (instead of table down), and then added that to filter and kept 1, 2, or 3. See attached. Hope this helps!

1 of 1 people found this helpful
• ###### 2. Re: Sorting/Filtering on Dimension and Sub-Dimension

Thanks Adam - the former is great, the latter I managed to work out myself even :-)

That only leaves one issue - how do I only shop the top 3 accounts by sum of their sub-accounts?

• ###### 3. Re: Sorting/Filtering on Dimension and Sub-Dimension

Hi Oli, I did that in my attached file too. I put Account on the filter shelf, and then created a Top filter for the Top 3 based on the sum of Value.

1 of 1 people found this helpful
• ###### 4. Re: Sorting/Filtering on Dimension and Sub-Dimension

Sorry Adam, I hadn't realised you included an attachment :-)

(edit - sorry please see my answer below, we need a Rank filter rather than a "normal" filter)

I swear I tried this - must be the combined field that did the trick, thank you very much!

I have one last issue however... I tried this on my actual data, and weirdly enough "Top 10" only gives me the top 9. "Top 5" gives 5, "Top 6" gives 6, "Top 7" gives 6 (still), "Top 8" gives 7, etc. - I can't share the data I'm afraid but would you have any idea what might cause this?

• ###### 5. Re: Sorting/Filtering on Dimension and Sub-Dimension

So sorry, I have to take this back - the reason it didn't work is that we can't do this using a filter as there are dynamic filters going on, i.e. we always need the Top X of what's currently being filtered (there's a country column in the original data, and you can select a country).

So I need another Rank that gives me the Rank of Account by Sum(Value). Any idea how we can achieve this? I've tried all possible combinations really...

• ###### 6. Re: Sorting/Filtering on Dimension and Sub-Dimension

Hi Oli,

Hope you are doing fine. I had a look at your problem and I too have faced the same kind of problem while sorting subcategories within categories. I had figured out a easy way to do this is to use the INDEX() function in tableau.

Steps to solve:-

1. Create a INDEX() function and converte it to discrete. Then drag this feild between the category and subcategory pill.

2. This would assign each sucategory with a index. Now, you can create a table calculation on the INDEX feild and set the following Compute using, Restarting every and At the level to appropiate granuality(See the solved workbook).

3. Drag the Index feild to filter shelf to select only the top 3 sub-account in each account and then drag the account to filter shelf to only show the top 3 accounts by value.

I tried to solve this problem with the best of my understanding of your problem statement and I hope this solves your purpose.

Try watching videos and reading blogs on INDEX(), as it is one of the most powerful functions in tableau.

1 of 1 people found this helpful
• ###### 7. Re: Sorting/Filtering on Dimension and Sub-Dimension
1 of 1 people found this helpful
• ###### 8. Re: Sorting/Filtering on Dimension and Sub-Dimension

Amazing!! How did I not know about this context thingy before. This is going to help me in so many ways, thank you!

That only leaves the "Top 10 is only giving me Top 9" issue but I can live with that... Will just change to "Top 11" to get 10. Is this a bug or am I missing something?

• ###### 9. Re: Sorting/Filtering on Dimension and Sub-Dimension

100 makes it 92. Weird!

• ###### 10. Re: Sorting/Filtering on Dimension and Sub-Dimension

Context filter do not always work in the way you want it work. Specially when sorting sub category within categories Context filter face a lot of problem. Hence I suggested you to use INDEX(), although a little longer but it will always genrate expected results.

• ###### 11. Re: Sorting/Filtering on Dimension and Sub-Dimension

Hi Rudranshu, Your INDEX calc works great without any other filters, but if you filter on other dimensions, it doesn't evaluate the way we want. For example, in your solution if you add sub-account to filter, and then select only sub-account 1, it will still show accounts D, C, and A, even though B should now be in the top 3. What's happening is that all filters are being evaluated concurrently. In this case you can set sub-account as a context filter, and then it will be evaluated first, and then the top 3 calculation on Account will be applied afterward.

Oli, It's hard to know exactly what's going on without seeing your data, but what's probably happening is that your top 10 filter is being applied to the whole dataset, instead of the subset that you've filtered down to. This is a great place to use Rudranshu's INDEX calc to get the top 10, instead of Top in the filter dialog. More info here: Finding the Top N Within a Category | Tableau Software

• ###### 12. Re: Sorting/Filtering on Dimension and Sub-Dimension

Hi Adam, I agree with you completely. For particular use cases like that it is better to use context filter with INDEX calc.

And you are also right about Oli current problem ( showing only 92 instead of top 100) this genrally is the case when the filter is being applied to the whole dataset instead oif a particular sub account.