Help need in IF / ELSE statement

[AgiGovind]

Hi Community,

i was trying to use IF ELSE for SLA date (Red, Amber, Green).

IF [SLA] = todate (datetimeadd (datetimetoday(),1, "days")) THEN "Green"
ELSEIF [SLA] = datetimetoday() THEN "Amber"
ELSEIF [SLA] < datetimetoday() THEN "Red"
ELSE "Others"
ENDIF

Example: if today is Friday (Amber) and my next SLA is Monday, Then Monday should fall under "Green"

But with above mentioned IF ELSE statement, Monday falls under "Others" (because it consider weekends)

Pls help, my logic should be : it should not consider weekends.  SLA dates are from Monday to Friday. 
Kindly advise how to modify the above statement.

Thank you! 

[dYoast]

The simplest thing to do would be to just check if SLA is greater than today.

    IF [SLA] > datetimetoday() THEN 'Green"

This would cover the cases of Friday, Saturday and Sunday.

Otherwise, you will have to check if today is Friday, Saturday or Sunday and adjust your datetimeadd accordingly.

[jrlindem]

I agree with @dYoast 

I was curious about the question and dYoast's response so I built a quick workflow to see it in action.  Here's what I did for illustrative purposes:

I think you should be able to adapt to add in your Service Level expectations from there.  Meaning, if your SLA is 5 days then you can compute the SLA_Date based on that from, say, the ticket_open_date and then compute that against today in the same way.

Hope that helps, -Jay

[Yoshiro_Fujimori]

Hi @AgiGovind ,

If you go one step further to consider national holidays as well as weekends, you may want to check this weekly challenge.

https://community.alteryx.com/t5/Weekly-Challenges/Challenge-252-Working-Days-Remaining/m-p/708644/h...

It aims to create a macro to calculate the number of business days between two dates.

You can apply the result (Positive, Zero and Negative) to the IF / ELSE statement.

[AgiGovind]

Thank you @jrlindem & @dYoast. for your comments. So the logic with SLA date i was looking for was T-1 (Red), T=1 (Amber), T+1 (Green). if any date greater than T+1 (White). Now this logic works fine for the SLA dates from Monday to Thursday. However, on Friday (T=1) and then  Monday considered as (greater than T+1/White) . but the actual formula should be Friday (T=1/Amber) and Monday (T+1/Green), and Tuesday (greater than T+1/White). So to achieve this we would need a additional line in my earlier IF ELSE statement to exclude the weekends. 

[jrlindem]

@AgiGovind Ah, gotcha.  Yes, this makes more sense for real world applications. Here's a revised workflow and logic to account for the behavior I believe you're after.  I did not include Holidays as @Yoshiro_Fujimori  recommended, but you can adapt from here. 

Use the Simulation Date text input tool to put in either today or another date you want to treat as if it was "today".  The other dates in the SLA Date Examples are just a span of dates to show the leading/lagging dates to check the logic.  You may need to update those if you choose a Simulation Date that isn't within that span.  I hope that makes sense 😅

Also attached for reference,

Hope that helps illustrate how you can use logic to get to where you need to be.  It's done long-form, to make it easier to show what I'm doing logically.

-Jay

[OllieClarke]

Hi @AgiGovind 

I think this is the simplest way is to make an updated [Today] field with this formula:

//check if today is Friday or Saturday
If datetimeformat(DateTimeToday(),'%u') IN('5','6') 

//If so then shift it to Sunday
THEN datetimeadd(DateTimeToday(), 7-tonumber(datetimeformat(DateTimeToday(),'%u')),'day')

//Otherwise keep it as is
ELSE DateTimeToday()
ENDIF

 You can then use your original formula except use this field instead of DateTimeToday() in your green clause

IF [SLA] = todate(datetimeadd([Today],1, "days")) THEN "Green"
ELSEIF [SLA] = DateTimeToday() THEN "Amber"
ELSEIF [SLA] < DateTimeToday() THEN "Red"
ELSE "White"
ENDIF

Hope that helps,

Ollie