Next occurring date for ID and find the days difference

[monish_chandra]

I have a table like shown below 

ID	Admit Date	Discharge Date	Encounter Days
1	02/01/2018	02/06/2018
1	03/13/2018	04/05/2018	35

How to find the number of days between the last discharge date and next admit date - I'm trying to do a LOOKUP of the next admit date for a patient .  So the 'New Admit Date' column = Last discharge Date - Next Admit Date (highlighted in orange). It should not consider another patient's admit date. eg; Patient ID 1 has 35 encounter days. Should I be using Multi Row formula to do this task? 

[LukeM]

Yes @monish_chandra the multi-row formula is what you need.

You can use Sort to sort by ID and then dates. And then use the multi-row formula and the Group By function (on ID) in the configuration to ensure you don't reference other patients' dates.

Hope this helps. Let us know how you get on.

Luke

[Thableaus · Accepted solution]

Hi @monish_chandra 

Would this work?

Cheers,

[LukeM]

Thanks @Thableaus - that's exactly as I was intimating.

@monish_chandra I suggest doing what @Thableaus has shown but make sure you format the dates first. Make use of the DateTime tool in the Parse category to create proper date fields so Alteryx can read the Dates properly as dates. This will simplify your workflow in the long run.

As you can see from the screenshot above, the dates haven't been sorted properly - hence the values for Patient_ID = 7 aren't calculating properly.

Hope this helps.

Luke