Forum Discussion
Solved
Find result nearest a target date
Hello, I have a table that includes the following data about transplants: ID of transplant Date of transplant One or more laboratory results (with the lab date) Table looks like this: ...
Hello Mythicos,
Can you please try this approach:
1. Calculate the Target Date
Target Date = [Dt GRF] + 1002. Difference from Target Date
Diff Labo #1 = ABS(DATEDIFF([Target Date], [Dt Labo #1], DAY)) Diff Labo #2 = IF(NOT(ISBLANK([Dt Labo #2])), ABS(DATEDIFF([Target Date], [Dt Labo #2], DAY)), BLANK()) Diff Labo #3 = IF(NOT(ISBLANK([Dt Labo #3])), ABS(DATEDIFF([Target Date], [Dt Labo #3], DAY)), BLANK())3. Nearest Lab Result
Day 100 MLL = SWITCH( TRUE(), [Diff Labo #1] <= [Diff Labo #2] || ISBLANK([Diff Labo #2]) && [Diff Labo #1] <= [Diff Labo #3] || ISBLANK([Diff Labo #3]), [MLL #1], [Diff Labo #2] <= [Diff Labo #1] || ISBLANK([Diff Labo #1]) && [Diff Labo #2] <= [Diff Labo #3] || ISBLANK([Diff Labo #3]), [MLL #2], [MLL #3] )Hope this helps!
Hello Mythicos,
Can you please try this approach:
1. Calculate the Target Date
Target Date = [Dt GRF] + 100
2. Difference from Target Date
Diff Labo #1 = ABS(DATEDIFF([Target Date], [Dt Labo #1], DAY))
Diff Labo #2 = IF(NOT(ISBLANK([Dt Labo #2])), ABS(DATEDIFF([Target Date], [Dt Labo #2], DAY)), BLANK())
Diff Labo #3 = IF(NOT(ISBLANK([Dt Labo #3])), ABS(DATEDIFF([Target Date], [Dt Labo #3], DAY)), BLANK())
3. Nearest Lab Result
Day 100 MLL =
SWITCH(
TRUE(),
[Diff Labo #1] <= [Diff Labo #2] || ISBLANK([Diff Labo #2]) && [Diff Labo #1] <= [Diff Labo #3] || ISBLANK([Diff Labo #3]), [MLL #1],
[Diff Labo #2] <= [Diff Labo #1] || ISBLANK([Diff Labo #1]) && [Diff Labo #2] <= [Diff Labo #3] || ISBLANK([Diff Labo #3]), [MLL #2],
[MLL #3]
)
Hope this helps!
Hello Sahir_Maharaj, I tried your solution and there were some problems with the treatment of blanks.
But I took what you proposed and changed the Switch arguments a little, by first testing the "blank" scenarios and it worked perfectly.
SWITCH(TRUE(),
ISBLANK(Chimerism[Diff Labo #2]) && ISBLANK(Chimerism[Diff Labo #3]), Chimerism[MLL #1],
ISBLANK(Chimerism[Diff Labo #3]) && ([Diff Labo #1] <= [Diff Labo #2]), Chimerism[MLL #1],
ISBLANK(Chimerism[Diff Labo #3]) && ([Diff Labo #2] <= [Diff Labo #1]), Chimerism[MLL #2],
([Diff Labo #1] <= [Diff Labo #2]) && (Chimerism[Diff Labo #1] <= [Diff Labo #3]), Chimerism[MLL #1],
([Diff Labo #2] <= [Diff Labo #1]) && (Chimerism[Diff Labo #2] <= [Diff Labo #3]), Chimerism[MLL #2],
Chimerism[MLL #3]
)
So thank you!