3 Replies Latest reply on Mar 19, 2018 5:48 AM by Jim Dehner

Problem with Date Different Calculation

Hi,

I would like someone to advice me what are wrong with my Tableau calculation.

Attached, please find my tableau workbook (ver 10.2 and 10.5) and an excel file.

In the Excel file

Column A (Patient NRIC) - Patient Identification

Column E (Collection Date) - Uric Acid Test Performed Date

Column F (Short Text) - Lab Test Name

Column G (Test Result) - Uric Acid Test Outcome

Column H (BaseCase) - Index Case, ie. The first time patient who had Uric Acid test perform

AIM : To calculate the next Uric Acid Test performed and calculate the proportion of patients who have their Uric Acid check in the next 6 months

I set up a calculated new variable 'UA_Test_Day's in Tableau to calculate the next Uric Acid Test with reference to the index case date. Index case is referred to the first time when Uric Acid is performed. Please see below screenshot.

and the partial output as follow :

The first red box is reference to Patient A2262152K. The UA_Test_Days for Uric Acid performed on 31/08/2017 was calculated wrongly.

The second red box is reference to Patient A5952195M. The UA_Test_Days for Uric Acid performed on 20/04/2015, 07/07/2015 and 13/08/2015 were calculated wrongly.

The BaseCase (Index Care - 1st time Uric Acid Test performed) will have NULL on UA_Test_Days.

Can someone assist me? Why some of the days are calculated wrongly?

Thank you.

• 1. Re: Problem with Date Different Calculation

good morning

I am confused on your expectations -

your formula is comparing each date to the one before it - is that what you wanted or did you want to compare each date to the first date for each patient

if it is the second the formula would be

DATEDIFF('day',  window_min(MIN([Collection Date])),LOOKUP(MIN([Collection Date]), 0))

and will return this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 2. Re: Problem with Date Different Calculation

Thanks Jim. The formula works well for me.

DATEDIFF('day',  window_min(MIN([Collection Date])),LOOKUP(MIN([Collection Date]), 0))

Can you let me know how to use "window_min" and the lookup with offset 0?

Thank you.

• 3. Re: Problem with Date Different Calculation

the 2 formulas are table calculations - windowmin looks at the whats in the viz and takes the min value but bear in mind that it is taking the min of the aggregate value in the expression - that aggregate can be any function sum(), avg(), min(), max() etc

Lookup with 0 just takes the value of the current position in the table - think like lookup , -1 goes back 1 position - lookup 0 is just the current position

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.