3 Replies Latest reply on Sep 7, 2018 3:09 PM by Tableau Team

# Create group after sorting

Say I have data like:

fruitsNumber bought
Apple5
Banana10
Peach3
Banana4
Apple1

I want to create a sorted table that has the max number bought for each fruit with a new grouped column like:

fruit MAX Number boughtfruit (group)
Banana10A
Apple5B
Peach3C

where A is assigned to the highest MAX Number bought value, B to the second highest.. and so on.

Is there a way to achieve this? So far my data ends up looking like:

fruitMAX Number Boughtfruit (group)
Banana10B
Apple5A
Peach3C

because the grouping happens independently of the sorting.

The answers below helped with this specific case. The issue I'm running into now is that I need to use these 'A', 'B', and 'C' labels in a different table, but  RANK_UNIQUE([MAX Number bought]) is reapplied to all rows. Example:

What I need:

fruitNumber bought fruit (group)
Banana10A
Banana3A
Apple1B
Apple5B
Peach1C
Peach3C

What I get:

fruitNumber bought fruit (group)
Banana10A
Banana3C
Apple1E
Apple5B
Peach1F
Peach3D
• ###### 1. Re: Create group after sorting

Try this. Create a calculated field RankFruit:

[RankFruit]

RANK(MAX([NumberBought]),'desc')

Create a new calculated field to assign a letter to the rank:

[Letter]

CASE [RankFruit]

WHEN 1 THEN 'A'

WHEN 2 THEN 'B'

WHEN 3 THEN 'C'

ELSE 'D'

END

3 of 3 people found this helpful
• ###### 2. Re: Create group after sorting

Hi,

Build a calculated field:

Group Assigned:

IF RANK_UNIQUE(MAX([Number Bought]))=1 THEN 'A'

ELSEIF RANK_UNIQUE(MAX([Number Bought]))=2 THEN 'B'

ELSEIF RANK_UNIQUE(MAX([Number Bought]))=3 THEN 'C'

END

Hope it helps.

Michael Ye

6 of 6 people found this helpful
• ###### 3. Re: Create group after sorting

Thank you!