Calculate days between 2 dates with a condition

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

Sample Data1.xlsx

Tips and Tricks

Accepted answers

ShankerV

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

All comments

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

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

Sample Data1.xlsx

Calculate days between 2 dates with a condition.yxmd

ShankerV

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

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!!!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335