# Tableau Calculated Field Help

Tableau newbie here would really appreciate some help with a calculated field.

Let's say I have some data:

Patient ID Appointment Date
10001 1/1/2019
10001 4/5/2018
10001 2/3/2021
10002 3/2/2019
10002 3/5/2018
10003 1/1/2019
10003 1/3/2021

I want to get the previous appointment date in a separate column, based on the sorted ascending appointment date per patient.

I'd like my output to be:

Patient ID Appointment Date Previous Appointment Date
10001 1/1/2019 4/5/2018
10001 4/5/2018 NULL
10001 2/3/2021 1/1/2019
10002 3/2/2019 3/5/2018
10002 3/5/2018 NULL
10003 1/1/2019 NUll
10003 1/3/2021 1/1/2019

Eventually, I want to get the average time between each appointment.

I've attempted using LOD functions with window functions, but I can't figure out how to partition based on Patient ID (but I could be doing it wrong). Would anyone have any ideas on how I can achieve the output I want? Thanks in advance!

The Above Scenario can be solved using sql query and pass the result to tableau as data source

with BT1 as (
Select * from dbo.Tbl_PatientDetail
)
,BT2 as (
Select * from dbo.Tbl_PatientDetail
)

Select
BT1.PatientID,BT1.AppointmentDate
,case when (
Select
count(*)
from
BT2
where
BT2.AppointmentDate<BT1.AppointmentDate
and BT1.PatientID=BT2.PatientID
)=0 then null
else (
Select top 1
Bt2.AppointmentDate
from
Bt2
where
Bt2.AppointmentDate<BT1.AppointmentDate
and BT1.PatientID=BT2.PatientID
) end as [Previous Appointment Date]
from
Bt1


Number of visits over the total tenure at a clinic would give you average time between visits. Just count the # of records (visits) per each patient. Seems like the actual date of visits is irrelevant.

Psuedo-code: Total time = current date - initial visit Total Time/Count(visit_records).

• As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
– Community Bot
Commented Oct 8, 2022 at 15:50