Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculation Business days between dates - Inside Error Messages

scnoters
6 - Meteoroid

Hi all,

 

I have already read some forum posts about calculating number of working days between two dates in the forum. For example this solution from @danilang 

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Business-Day-formula/td-p/484513

 

My setup is as follows:

scnoters_1-1614601825561.png

 

The dates must be at least 10 working days (If only weekends can be considered, this is sufficient) between them, if this is not the case the error message should ask the user to select new dates. If at least 10 working days between the two dates, the whole workflow should be executed. Is this possible?

 

If this is not possible, I would have to solve it via the Message tool. However, this would not be so user-friendly.

 

Thanks!

 

Regards,

Scnoters

 

6 REPLIES 6
danilang
19 - Altair
19 - Altair

Hi @scnoters 

 

If you are concerned with just business days, you can set the Error Expression to some thing like 

 

DateTimeDiff([DeadLine],[StartDate],"days") <14 

 

since any two dates that are at least 14 days apart will always have a minimum of 10 business days between them

 

You can also get a bit fancier and check the day name of [DeadLine] with %a.   If it's Sat or Sun you can adjust the comparison to 12 or 13 days.

 

Dan

 

 

DanielG
12 - Quasar

I do mine a bit different because of Holidays.  I learned about this somewhere else on the community, but at this point I dont recall which prior thread taught me about it.  Maybe it was something @danilang had previously written up???  🙂

 

I generate rows for the dates in between "Start" and "End" then I filter our the Saturdays and Sundays with a formula and then filter out my company's specific holidays with a static YXDB file of holidays going well into the future.  I built it once years ago, and I apply it any new creation where I have to take Holidays out of any calcs.  (A little bit of upfront labor to create the list, but as long as your holidays are consistent there should be minimal upkeep required in the future.)

 

Once I remove all the non-work days, I just sum them back up to remove all the extra generated rows to get workday counts. 

 

DanielG_0-1614627519555.png

 

scnoters
6 - Meteoroid

Hi @danilang 

Thank you very much for your answer.

 

Well now of course I am being punished for simplifying my whole workflow and goal.

 

In total, several error messages have to be set up and different durations are necessary. As you mentioned, one could distinguish which days have the start and end date and calculate the difference in days and weeks and use these to set up a formula with various ifs conditions.

 

If I have tested it correctly, my If forms also work. You could probably formulate the whole thing differently, but this was the easiest way for me.

scnoters_0-1614663580818.png

(I used a screenshot from the Formula Tool, as this is clearer than the expression in the error message. I have removed the very first if for the error message)

 

 

Unfortunately, once you have the first solution, you always want more. Would there be a possibility for my case to carry out this calculation also including holidays within the interfaces tools?

 

Regards,

Scnoters

scnoters
6 - Meteoroid

Hi @DanielG 

 

Thank you for your answer.

 

I understand your variant, but this only seems possible to me personally if the workflow starts correctly and is then also completed in the standard case. My goal is that the user selects data in the app and then the message appears that the data was selected incorrectly and that he must select the data again and that the Alteryx workflow is only started correctly when all conditions are met.

 

Is there a way to use the static YXDB file you mentioned in the error message expression?

 

Regards,
Scnoters

 

danilang
19 - Altair
19 - Altair

Hi @scnoters 

 

Unfortunately, it's not possible for the interface tools to act on more than one record at a time.  This record consists solely of the values that the user has selected for each of the connected tool.  There's no way to inject a list of holidays to exclude.   

 

It's possible to do it in a single error message tool but it would be ugly.  For the fixed holidays i.e. Jan 01, you can just add clauses that check if each of the holidays is in the start-end range and then modify the range by one day.   For the dynamic ones, 3rd Mon of Feb, you'd need a formula that calculates the date for the current year and then check if it's in the range.  Possible...but very ugly and convoluted.  And calculating the holidays that aren't directly linked to the Julian calendar, Good Friday, Yom Kippur, Ramadan, Chinese New Year, will involve even more mathematical gymnastics

 

Dan       

scnoters
6 - Meteoroid

Hi @danilang 

 

Thank you very much for your answer. It is a pity that this is not possible with the interface tools.

 

Yes, I see the approach, but this is then really no longer nice.

 

Regards,

scnoters

Labels