How can I add a row number for a specific field

Question

Sample Data in SQL:

DOB         Status       Policy StartDate   EndDate
1/05/1983   Lapsed       P1     5/05/2015   5/06/2016
1/05/1983   New Business P2     3/05/2016   
2/04/1999   Lapsed       P3     5/02/2016   10/06/2017
2/04/1999   New Business P4     10/07/2017  
3/06/1972   Lapsed       P5     6/07/2016   15/12/2017
3/06/1972   New Business P6     1/10/2017   
4/12/1954   Lapsed       P7     7/03/2017   1/03/2018
4/12/1954   New Business P8     1/03/2018   

Goal: Add descending number based on DOB field. The expected result suppose to be like below.

Unfortunately I only can get number '1' in column #.

For column #, I have tried using index(), Window_Count (Countd(DOB), 04,0), Runnning_Total (Table Down, Pane Down, Specific Dimension : DOB), however nothing works.

I'm using Tableau desktop/server 10.0.

Answer 1

Hi @Noob,

You can use RANK_DENSE function:

"The function returns the dense rank for the current row in the partition. If the values are identical they are assigned an identical rank, but no gaps are inserted into the number sequence. You can use the optional 'asc' | 'desc' argument and specify descending or ascending order. By default it is descending.

For example: set of values (6, 9, 9, 14) would be ranked (3, 2, 2, 1)."

Following is the query you want:

RANK_DENSE(SUM(FLOAT([DOB])),'asc')