1
$\begingroup$

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!

$\endgroup$

2 Answers 2

1
$\begingroup$

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 
$\endgroup$
0
$\begingroup$

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).

$\endgroup$
1
  • $\begingroup$ 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. $\endgroup$
    – Community Bot
    Commented Oct 8, 2022 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.