CODEWITHSUNDEEP
tableau-api
J. Stott
J. Stott

Reputation: 304

Ranking duplicates in Tableau

I have a table with three columns Id, A and B. Id is unique, but I want to identify and rank duplicates across Columns A and B.

So for the following data

 Id | A | B
----|---|---
  1 | a | b
  2 | a | c
  3 | a | b
  4 | c | b
  5 | a | c

I want to get the following 'ranks' for each Id

 Id | Rank
----|------
  1 |    1
  2 |    1
  3 |    2
  4 |    1
  5 |    2

I though I could achieve this using an LOD but trying this doesn't work. Any ideas on how to achieve this?

I basically want the row with the lowest Id for a fixed A and B to have a rank of 1; the next lowest Id for the same fixed A and B to have a rank of 2 etc.

The following was what I attempted but it's not working - I need to run an aggregate on Id for the RANK function to work but I can't think which to use.

{
    FIXED [A], [B] :
    RANK([Id])
}

Upvotes: 0

Views: 1346

Answers (1)

Jose Cherian
Jose Cherian

Reputation: 7737

There are two issues with your calculation.

  1. RANK is a table calculation and Table calculations are not allowed to be used in LODs
  2. A Table calculation only accepts an aggregation or a constant, not just a field name

This kind of scenarios can be easily handled with Table calculations RANK or INDEX. For example, create a calculated field INDEX with formula INDEX() and build the view as below.(Please note that INDEX is calculated using 'Pane Down')

enter image description here

Upvotes: 1

Related Questions