Post by pvansicklerI am using RelativeDate to calculate a new data based on a
priority calculation Ex: High Priority = Todays date + 4
days, Normal Priority = Today's date + 10 days, Low
Priority = Today's date + 20 days.... I want the new dates
not to land on a weekend date but rollover to the
following Monday... Any advice or guidance in the correct
direction is appreciated...
I'm not fantastic at this stuff, but I think the following
may be of use. I'm afraid I don't have time to type out the
complete response and test it, but if I start it hopefully
you will see the pattern and be able to complete the rest...
For the following I will assume that the date column on your
report that contains the original date is called "odate" and
the field that contains the priority is called "priority"
and contains either "H", "N", or "L" for High, Normal, and
Low respectively.
Select both of these columns in the back end and then delete
their fields if necessary in the frontend (after clicking
'Return')
Then Add a Computed field to the 'details' area which will
display the 'Complete By' date, with something along the
lines of the following expression:
CASE ( table.priority
WHEN 'H' THEN
( CASE ( DAYNAME ( table.odate )
WHEN 'Tuesday' THEN RELATIVEDATE( table.odate, 6
)
WHEN 'Wednesday' THEN RELATIVEDATE (table.odate,
5 )
ELSE RELATIVEDATE (table.odate, 4) )
)
WHEN 'N' THEN
( CASE ( DAYNAME ( table.odate )
WHEN 'Wednesday' THEN RELATIVEDATE( table.odate,
12 )
WHEN 'Thursday' THEN RELATIVEDATE (table.odate,
11 )
ELSE RELATIVEDATE (table.odate, 10) )
)
WHEN 'L' THEN
( CASE ( DAYNAME ( table.odate )
WHEN 'Monday' THEN RELATIVEDATE( table.odate, 21
)
ELSE RELATIVEDATE (table.odate, 20) )
)
)
Basically, what I've done is checked to see what the
priority is to see how many days to add. Once I know how
many days to add, I work out which days of the week adding
that number of days would cause it to fall on a weekend.
For example, if I needed to add one day, I would need to do
something with Friday as that would fall on Saturday.
Instead of adding 1, I've added 3 so that it is pushed
forward to the next Monday.
I don't know for sure that the above example is strictly
correct, or that the computed field will work with the
nested select statement very well, but hopefully it's a
direction for you to work in.
Kind Regards and Good Luck,
Tommy Long