How to rank my rows then take a specific value upon max rank to a new calculated field in Tableau

77 views Asked by At

I am working on a data in Tableau,my data is like the following:

MRN   CHECK-IN  CHECK-OUT   WARD
111   DATE 1    DATE 2      15
111   DATE 2    DATE 3      16
111   DATE 3    DATE 4      20
222   DATE 1    DATE 2      14
333   DATE 1    DATE 2      11

Table1

I have MRN for patients and check-in and check-out in a specific ward then if the patient transferred to another ward a new record will be added for the same patient.

I need to make a new calculated field contains ranks per patients depends on the check-in like the following:

MRN   CHECK-IN  CHECK-OUT   WARD    Rank
111   DATE 1    DATE 2      15      1
111   DATE 2    DATE 3      16      2
111   DATE 3    DATE 4      20      3
222   DATE 1    DATE 2      14      1
333   DATE 1    DATE 2      11      1

Table2

Then I want to add another calculated field to be as followed:

MRN   CHECK-IN  CHECK-OUT   WARD    Rank    new checkouts for max rank
111   DATE 1    DATE 2      15      1       DATE 4
111   DATE 2    DATE 3      16      2       DATE 4
111   DATE 3    DATE 4      20      3       DATE 4
222   DATE 1    DATE 2      14      1       DATE 2
333   DATE 1    DATE 2      11      1       DATE 2

Table3

I hope my question is clear

1

There are 1 answers

0
Vishal Gupta On

if your main aim is to get last max date column then rank is not necessary

max checkuout= {fixed MRN: max(CheckOut)}