CODEWITHSUNDEEP
row-numbercumulative-sumtableau-apirownum
Adhitya Sanusi
Adhitya Sanusi

Reputation: 119

Adding Row Number for Specific Field

I have the data below 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

I need to add descending number based on DOB field. The expected result suppose to be like below.

enter image description here

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.

Thanks all for the help.

Upvotes: 1

Views: 6381

Answers (1)

Armin
Armin

Reputation: 363

Use RANK_DENSE function:

"Returns the dense rank for the current row in the partition. Identical values are assigned an identical rank, but no gaps are inserted into the number sequence. Use the optional 'asc' | 'desc' argument to specify ascending or descending order. The default is descending.

With this function, the set of values (6, 9, 9, 14) would be ranked (3, 2, 2, 1)."

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

enter image description here

Upvotes: 2

Related Questions