5 Replies Latest reply on Feb 2, 2018 3:35 AM by Zhouyi Zhang

# Complex Calculation on Date

Hi,

I need some assistants on calculating the number of days perform the Uric Acid test.

Column A represents different patients.

Column D represents when the patients visit the hospital Outpatient clinic. The first visit to the clinic is considered as the index case.

Column F represent the days when Uric Acid Test is performed. The days are calculated based on the difference between the performed date and index case date

I need to use Tableau auto calculate Column G and plott the monthly trend on Uric Acid (UA) Test performed at least once per 6 months.

Can someone advise me how to do these in Tableau?

Thank you.

• ###### 1. Re: Complex Calculation on Date

Hi, Wui

Hope this helps

ZZ

• ###### 2. Re: Complex Calculation on Date

Hi Wui Heng Goh,

First create the Index Case date as

{fixed  [Patient ID],[Gender] : min([Visit DateTime])}

Using this date, create a dimension to identify if the Uric Acid test happened within 6 months (180 days) or not is created and add it to the filter:

Uric Test in 6 months

if  isnull([Uric Acid Test Data Time] - [Index Case Date])  then NULL

elseif [Uric Acid Test Data Time] - [Index Case Date] <180 then 1

elseif  [Uric Acid Test Data Time] - [Index Case Date]  > 180 then 0 end

Then create a measure as  # Uric Tests in 6 months: SUM([Uric Test in 6 months ]) .

Create a report with visit month and # Uric Tests in 6 months.

Please find attached the .twbx file for details.

A few things which I have assumed as they were not clear from the question:

- If the Uric test was performed on the first visit date itself should it be counted? As of now I have considered it as a test within 180 days

- Should the result show count the count of tests or patients? I noticed there is a difference in the distinct patient count and tests

Regards,

Artee

• ###### 3. Re: Complex Calculation on Date

Thanks a lot for your help. The result should show by visits and not by patient.

• ###### 4. Re: Complex Calculation on Date

Thank you, Zhouyi.

• ###### 5. Re: Complex Calculation on Date

does this help?

ZZ