topic Re: Calculate days between 2 dates with a condition in Alteryx Designer Desktop Discussions

Calculate days between 2 dates with a condition

itsmeanuj — Thu, 22 Dec 2022 06:01:09 GMT

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

Re: Calculate days between 2 dates with a condition

ShankerV — Thu, 22 Dec 2022 06:04:47 GMT

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

Re: Calculate days between 2 dates with a condition

ShankerV — Thu, 22 Dec 2022 06:23:01 GMT

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

Re: Calculate days between 2 dates with a condition

ShankerV — Thu, 22 Dec 2022 06:27:23 GMT

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

Re: Calculate days between 2 dates with a condition

ShankerV — Thu, 22 Dec 2022 06:30:38 GMT

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

Re: Calculate days between 2 dates with a condition

ShankerV — Thu, 22 Dec 2022 06:42:43 GMT

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

Re: Calculate days between 2 dates with a condition

itsmeanuj — Thu, 22 Dec 2022 06:56:29 GMT

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

Re: Calculate days between 2 dates with a condition

ShankerV — Thu, 22 Dec 2022 06:59:20 GMT

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

Re: Calculate days between 2 dates with a condition

itsmeanuj — Mon, 26 Dec 2022 15:38:22 GMT

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

Re: Calculate days between 2 dates with a condition

ShankerV — Mon, 26 Dec 2022 15:50:48 GMT

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

Re: Calculate days between 2 dates with a condition

itsmeanuj — Mon, 26 Dec 2022 16:07:57 GMT

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

Thanks,

Anuj

Re: Calculate days between 2 dates with a condition

ShankerV — Mon, 26 Dec 2022 16:55:28 GMT

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

Re: Calculate days between 2 dates with a condition

ShankerV — Mon, 26 Dec 2022 17:01:35 GMT

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

Re: Calculate days between 2 dates with a condition

itsmeanuj — Wed, 28 Dec 2022 05:58:35 GMT

Thank you so much Shanker! It worked as expected.

Re: Calculate days between 2 dates with a condition

ShankerV — Wed, 28 Dec 2022 06:14:11 GMT

Hi @itsmeanuj

Welcome, happy to support anytime!!!!

Happy new year in advance!!!