12-17-2012 09:20 AM - edited 03-01-2019 08:57 AM
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?
12-17-2012 11:43 AM
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!
12-18-2012 06:46 AM
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.
12-20-2012 07:09 AM
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!
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide