I am struggling with my datediff calculation. I have a dataset where there are test results performed for patients who test on multiple occasions (the number is not consistent between individuals). I need to calculate the difference in dates between two result values: "Non-reactive" to "Reactive" and then "Reactive to Non-reactive".
So far, I was able to use this calculation for date difference between sample collections:
DATEDIFF('day',LOOKUP(ATTR([Collection Date]),0),LOOKUP(ATTR([Collection Date]),1))
This generates the correct difference in dates when I place the patients and samples together; however, I get the date difference of the next patient as well when I only need the difference between one patient's samples.
So, 1) how can I resolve this above issue and 2) how can I include the result values in the calculation so I'm only looking at the date difference between either "Non-reactive" to "Reactive" or "Reactive to Non-reactive"?
Appreciate any help with this!
You'll need to upload a workbook so we can work together.
You can set the table calc settings so that they restart every [PatientID], and that will stop looking forward at the end of a patient's rows. I could show you if I had a workbook.
As for the reactive vs non-reactive, I would really need to see a workbooks so I can see how the data is set up.