Hello Team,
I'm trying to calculate the date in which an item needs to be addressed (actioned) depending on the status of this item (Assigned/ On Hold) and the number of days you have to do that (per rule), so as not to have it as out of SLA (Service Level Agreement). The tricky part is that if the item is "Assigned" you have 1 day to action that item (24 Hrs) this is given by the "Assigned Date" column, on the other hand if the item is "On Hold" the date is given by the "Due Date" column. I used to do this using the Excel function "=WORKDAY(start_date,days, holidays)" where working days exclude weekends and any dates identified as holidays, but in Alteryx I cannot find any function like that. The field days ("1" in the example below) tells me I have 1 day to action that item.
I've seen the other topics regarding Calculate Business Days but is not what I'm looking for since the results is a number instead of a date.
Your assistance is highly appreciated Alteryx Community.
Thank you in Advanced,
Solved! Go to Solution.
Hi @almartinez,
This is what you are looking for. Because there is no weekday formula you need to fake one.
IF [Status]='Assigned'
THEN
(IF DateTimeFormat([Assigned Date],'%a')='Fri'
THEN DateTimeAdd([Assigned Date],3,'day')
ELSEIF DateTimeFormat([Assigned Date],'%a')='Sat'
THEN DateTimeAdd([Assigned Date],2,'day')
ELSE DateTimeAdd([Assigned Date],1,'day')
ENDIF)
ELSEIF [Status]='On Hold'
THEN [DueDate]
ELSE Null()
ENDIF
Hey @almartinez
I've worked this up in an Alteryx flow (attached). It looks very intimidating, but I'll talk you through it. You can convert much of this to a macro to do what you're looking for, but I didn't want to complicate it too much - we can always convert this to a macro with you as a followup
Steps:
I've attached the workflow to this post - you should be able to make this work from here outwards, and we can always clean this up by turning it into a macro later, and you could then also add features like multi-region (for different holiday calendars), and 2 and 3 day SLAs.
@almartinez - if this gets you to a solution, would you mind marking this as solved - or if there are still additional questions, then feel free to reply on this thread.
Cheers
Sean
This is great, thank you Andrew. I've just used some of the formula you wrote and works fine!
Your welcome @almartinez. The only thing it should be missing are the holidays and you should be set.