There's a defined function in v10 called WorkingDays which requires a little setup (tell Monarch what day the week starts on, define public holidays etc) but then will do what you need out of the box.
To get 3 working days prior to a given date, use this formula:
For the function Weekday(date[,startday]) where startday defines when the week starts (1=Sun, 2=Mon). We specify Mon (2) so that Sat and Sun become 6 & 7 respectively.
If we subtract 3 days from a given date and WeekDay returns 5 (Fri) or greater, we know we are "subtracting" over a weekend and thus must subtract 5 days to make up for that weekend, otherwise we just subtract the original 3 days from the given date.
Hope that helps!
Alternatively, you could write the formula as:
Same principle, just a little shorter.
Hi Nick and others
My problem is that if i used the formula, it identifies the Date parameter, but i need to create a process to extract them in 3 parts.
IE: I have 300 records, Based on the T1, T2, T3 that would let us say break it down as T1 (50 Records), T2 (100 Records) and T3 (150 Records)
I need to create an extraction which works by taking my Trade Date and where the parameter is T+1 then only extract those records.
SO if trades are ie: 8/24, 8/23. 8/23, etc the trades show up in the groupings below based on using today's 8/25 date
The formulas would kind of do the following:
so in theory Tab 1 would take all T1 trades, Tab 2 would have T2 trades while Tab 3 would have T+3and greater
If I use those 3 as fields, it will only calculate the date in each field, and the extraction would not work, how do I extract based on meeting exact criteria where Trade Date is T1, vs displaying the date in a field in addition to the T2, T+3?
can anyone please help, this is frustrating. I need to be able to extract data based on the T1, T2, T3 criteria based on the trade date that is in place.
The T1, T2, T3 dates show the date at hand, but now I need to age them.
Example as follows:
The formula doesn't help if i cant pull the trades.
In theory the Trade Date = yesterday (8/25/09) is the T+1,
while trade Date = 8/24/09 is T+2,
and anything from friday 8/21/09 and backwards would fall in T+3 category.
Please let me know how I can extract the data.
Not sure the formula above helps.
The problem i have with date is as mentioned, i need to take out the Weekend out of the equation.
Would you have a sample process that will enable me to calculate the following: (anything greater then 3 would be grouped up as >=T+3)
8/25/2009 (in theory this would be T+1 from today())
8/24/2009 (T+2 from Today())
8/21/2009 (T+3 from Today())
8/20/2009 (T+3 from Today())
8/17/2009 (T+3 from Today())
8/19/2009 (T+3 from Today())
8/18/2009 (T+3 from Today())
8/10/2009 (T+3 from Today())
do you have a sample formula that would take into account the dates below and link them up to today's date excluding weekends?
In theory it would subtract today's date -1, -2, -3 (excluding weekends) and if it matches the below date then it can be pulled?
is it also possible to add a parameter for excluding holidays?
Your profile suggests you have v10. This includes user defined functions. If you are in the table view, and go to the Data menu, select the User Defined Functions option, then click on import from the dialog box, select the default external model, then select the following functions:
Click OK. You'll see that it imports all the required additional functions:
WorkingDaysComputeDaysOffInRange (and about 10 others)
(comment)... This is essentially a list of holidays that must be edited before starting to use the Working_Days user defined function.
Here are the other user defined functions that also need to be edited before using Working_Days:
WorkingDaysComputeDaysOffInRange(startdate,enddate) Returns the number of days off in the range of dates from startdate to enddate. Days off are defined as days that would ordinarily be working days, but are not worked because they are holidays or vacation days. This must be edited before using the Working_Days user defined function.
It's really just a matter of working through. Once you know the number n of working days between Today() and TradeDay, then for a given trade day you can return T1, T2, T+3... with a simple lookup field or even a nested if:
Strong links mean that Monarch will only keep a pointer to the source functions in your model. The functions will not be in your model. Every time you run the model, Monarch will need to have access to the source UserDefinedFunctions.xmod model in the right position, and any changes made in this model will affect yours.
Weak links will mean that the functions are copied in to your model, but that Monarch will alert you if there are newer versions of the functions in the source model.
No link means that the functions are copied over and the link is lost. This ensures that your model is portable but also, if you are planning on building lots of models where you define your Working Days parameters, could lead to you repeating work.
As a first attempt, I'd try "none" and get on with the job, failing that, try "weak".
I was wondering rather then building some intense complicated process.
What I was thinking is I could perform the following functionality by changing the process using today's date to identify the previous trade dates. Then what I can do is create a process to say if Trade Date = T1 then show those, then if Trade Date = T2 then show only those, and Trade Date >= T+3 to show those trades. Using below 3 would create the Group by that Grant mentioned, but now I need to be able to extract those parameters.
I thought a Filter was the way to go but it appears that somehow records don't match vs. what the filter I had in place, 197 to 195 records (2 missing)
This is what I was thinking:
That would = T+3
However this does not include the items > 3 days,
how can I adjust this formula to be >=3?
This would = T+2
This would = T+1
If you use the formula to establish whether the record it T1, T2 or T+3 you can also use it to set a flag to identify the record by those categories.
This would give you a grouping flag. You really only need T1 and T2 (based on your description) since anything else would be T+3 (assuming there would not be any transactions at a weekend).