Calculate days between 2 dates with a condition

[itsmeanuj]

Hi,

I want to calculate Hold days (see column Status code) before 1st shipment. Here are the conditions

1. Hold days should exclude weekends
2. Hold days should be calculated between the status code "Hold" and any other status (New, ready, etc)

Expected Outputs

1. for Patient ID 10001, hold days will be 2 days (12-09-2020 - 12-07-2020)
2. For patient ID 10002, Hold days will be 22 days ((10-07-2021 - 09-09-2021 - weekend days = 20 days) & (10-14-2021 - 10-12-2021 = 2 days)).

We do have thousands of patient IDs in our data and want to calculate hold days for all of them. If someone can guide on how we can achieve this in Alteryx?

Thanks,
Anuj

[itsmeanuj]

Sure! so if you see my original post (point 2 in expected output) - once the shipment is done on 10/14/2021, we don't have to consider Hold status after that. Idea is to calculate the hold days only till the first shipment is done. Please consider column D as the expected output in the attached excel. 

Expected Outputs

1. for Patient ID 10001, hold days will be 2 days (12-09-2020 - 12-07-2020)
2. For patient ID 10002, Hold days will be 22 days ((10-07-2021 - 09-09-2021 - weekend days = 20 days) & (10-14-2021 - 10-12-2021 = 2 days)).

Thanks,

Anuj

[ShankerV]

Hi @itsmeanuj 

Please find the revised solution where Hold will not be considered after Shipment for each Patient ID.

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @itsmeanuj 

I have added just 2 tools to amend the output.

Used this formula in Multi row formula 

if ([Status Code]="SHIPMENT")
then 1
elseif [Row-1:Patient ID]=[Patient ID] AND [Row-1:New Field]=1
then 1
else 0
endif

Then one more main note, I have updated my alteryx version hence the date format in the input file was in MM-DD-YYYY but my current alteryx version read the file correctly as YYYY-MM-DD as per alteryx format.

Hence I have removed my Date-Time format tool, in the below location.

Hope my solution will work in your system also as per the same input file used at your end.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[itsmeanuj]

Thank you so much Shanker! It worked as expected.

[ShankerV]

Hi @itsmeanuj 

Welcome, happy to support anytime!!!!

Happy new year in advance!!!