Previous working day - date calculated variable

[SimonOzzy]

Here's a nice tester / though probably way simpler than I'm finding it. I'd like to create a "previous working day" date calculated variable.

Despite having done advanced Tidal course with much harder tutorials than this, I can't get it to work for all the below cases:

If it's a Tues to Saturday :

  Subtract one day Monday -- Friday

If it's a Sunday :

  Subtract two days - Friday

If it's a Monday :

  Subtract three days - Friday

Any ideas?

[rami_rgare]

Simon,

In the past I had a similar request to create/calculate the Previous BusinessDay; and I think i've implemented it as in below.

First you need to make sure that you have the following pre-defined;

1) Calendar of Mon-Fri ( i think its a built in Cal)

2) Holidays_YourCountry/Business Calendar ( I think there are couple of built-in Holiday calendars, but you may create your own to meet your business needs).

3) Daily Calendar (Built in calendar as well which covers all days of any week regardless of holiday/weekend)

4) Weekend Calendar ( built-in which covers Sat and Sun)

Now, back to your requirement, To create a Calculated variable of any type, you need to do so based on a calendar. Therefore you need to create a new calendar and call it Previous_BusDay_Calendar (Group Calendar) as follows;

1) Include Daily Calendar (no ajjustments/offset)

2) Jump to Condition Tab and click on Add;

a- Add the Weekend Calendar (Take Action: Previous Calendar, Calendar: 0, and select Monday to Friday Calendar) 
         Click OK to save first condition.

b- Add again and select Holidays_YourCountry/Business Calendar (Properties same as in -a above), Click OK to save

3) Click OK to save the newly created Group Calendar; Previous_BusDay

Now to calculate the previous business day and log it into a variable;

1) From Variables; Create a new Variable and give it a name like Previous_BusDay_Variable or anything unique to your env.

2) Create as Date Type (Properties as below)

a- Starting With : Production Date

b- Offset Minus 1

c- Custom Calendar and then select the calendar you've created above; Previous_BusDay_Calendar

d- Click Add to save your selection and watch the the test offset as it should show the date of the previous business day based from the current date.

Thats it. you now have a variable as required.

Key Hint: the Offset Minus 1 is what will always refer to the last previous business day.

Have fun!

[SimonOzzy]

Hi Rami

Thanks for the detailed and helpful answer. I understand how it's intended to work but for some reason I can't get it to work that way. It's fine for weekday's but then testing the variable for Sat/Sun appears to "compound the offsets" and gives Thursday instead of Friday.

I've spent a good deal of time understanding this and trying a few other permutations of what you provided but can't seem to get the intended results.

[SimonOzzy]

I've worked it out for myself through trial and error and arrived at this simple yet mysterious solution:

1) Create a calendar as Tuesday to Saturday

2) Variable: Production Date -> Offset Minus 1 -> Custom Calendar -> Tuesday to Saturday

Results of variable testing:

   Mon -> Fri ; Sun -> Fri; Sat - Fri ; Fri - Thu etc

I won't pretend to understand why this works, but it does!