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

[ShankerV]

hi @itsmeanuj 

I have gone through the sheet. So when HOLD comes for an Patient ID and the date needs be calculated for the next status.

Do you need to exclude only weekends. Not any public holidays in additional right?

The sheet shared is in MM/DD/YYY format

Many thanks

Shanker V

[ShankerV]

hi @itsmeanuj 

Please find the below expected solution.

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

[ShankerV]

Hi @itsmeanuj 

Explaining how my solution will work for huge datasets also.

Step 1: Input

Step 2: Record ID tool

As I noticed the same patient has HOLD multiple times, I need this tool.

Step 3: DateTime tool

As your date is other format, changing to alteryx YYYY/MM/DD format

Step 4: Multi Row formula tool

if ([Status Code]="Hold")
then [Row+1:DateTime_Out]
else null()
endif

Step 5: Generate Rows tool

[diff] <= [next date]

datetimeadd([diff],1,"days")

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

[ShankerV]

Hi @itsmeanuj 

Step 6: formula tool

datetimeformat([Diff],"%A")

Step 7: Filter tool

!Contains([Day],"Sunday") and
!Contains([Day],"Saturday")

step 8: summarize tool

step 9 : Formula tool

[Count]-1

step 10: Join tool

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

[ShankerV]

hi @itsmeanuj 

Step 11: Union tool 

Step 12: Select tool

Then you get the result:

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

[itsmeanuj]

Thank you so much Shanker! I will go through each step to reach to the desired output. Is it possible for you to share the workflow which you have created for this?

Thanks,

Anuj

[ShankerV]

hi @itsmeanuj 

I was also trying to share the workflow but not able to save due to the below error.

Could you please replicate the steps as I have explained in very detail.

Feel free to ask questions if you have any... Happy to support.

Many thanks

Shanker V

[itsmeanuj]

Hi Shanker,

Thanks for your response. Your solution works as expected except for 1 condition. We need to consider Hold days only before the shipment. e.g. for Patient 10002, we should not consider this situation below as the shipment was done on 10-14-2021.

10002

12-06-2021

Hold

Can you please help me achieve that?

Thanks,

Anuj

[ShankerV]

Hi @itsmeanuj 

Sure, we can achieve.

Can you please explain the requirement with an example little in detail so that I can alter the formula.

Many thanks

Shanker V