Case Function

[Wiz Bang Computers]

If this is in the wrong place I appolgize. I am trying to set up a calculated field using the CASE fucntion. My goal is to automate the calculation of the payment date. Payments received after 3pm go two business days out, prior to 3pm they go 1 business day. I am running into problems with the issue of the weekend. Here is what I tried to put together, but it is not working:

Case (

cDAY OF WEEK = "7" and

CREATION TIME ≤ "3:00:00 PM" ; CREATION DATE + 3;

cPAYMENT DATE = "7" and

CREATION TIME ≥ "3:00:00 PM";CREATION DATE + 3)

or

Case (

cDAY OF WEEK = "2" or "3" or "4" and

CREATION TIME ≤ "3:00:00 PM" ;CREATION DATE + 1;

cDAY OF WEEK = "1" or "2" or "3" and

CREATION TIME ≥ "3:00:00 PM";CREATION DATE + 2)

or

Case (

cDAY OF WEEK = "4" and

CREATION TIME ≤ "3:00:00 PM" ;CREATION DATE + 1;

cDAY OF WEEK = "4" and

CREATION TIME ≥ "3:00:00 PM";CREATION DATE + 4)

or

Case (

cDAY OF WEEK = "5" and

CREATION TIME ≤ "3:00:00 PM" ;CREATION DATE + 3;

cDAY OF WEEK = "5" and

CREATION TIME ≥ "3:00:00 PM";CREATION DATE + 4)

Can anyone provide some guideance?

Thank you,

Scott

[IdealData]

Case (

cDAY OF WEEK = "2" or "3" or "4" and

CREATION TIME ≤ "3:00:00 PM" ;CREATION DATE + 1;

cDAY OF WEEK = "1" or "2" or "3" and

CREATION TIME ≥ "3:00:00 PM";CREATION DATE + 2)

I didn't look long at this however the second CASE statement should read:

Case (

(cDAY OF WEEK = "2" or cDAY OF WEEK = "3" or cDAY OF WEEK = "4") and

CREATION TIME ≤ "3:00:00 PM" ;CREATION DATE + 1;

(cDAY OF WEEK = "1" or cDAY OF WEEK = "2" or cDAY OF WEEK = "3") and

CREATION TIME ≥ "3:00:00 PM";CREATION DATE + 2)

Notice that each OR operation must specify the value to compare with and I have enclosed all the ORs into braces so they are evaluated together. I haven't checked this for syntax or logic, but I might be the clue you're looking for.

[Razumovsky]

Assuming that no payments would be created on saturdays or sundays, the following should do the trick (remember to set calc field as date):

Let(

d = Case(GetAsNumber(CreationTime) ≤ 61200; CreationDate + 1; CreationDate +2);

Case(DayOfWeek(d)=1 or DayOfWeek(d)=7; d+2; d)

)

-Raz

[Wiz Bang Computers]

Raz,

Thanks, this did the trick!

Scott

[LaRetta]

I can't help but respond here ... when you put limitations on a calculation, such as 'it'll work as long as you don't have a creation date on a weekend,' it opens one up to E. Murphy, Jr. Why? Because the first time some secretary doesn't enter the figures until she comes in on a weekend, it'll break. Surprise! What if someone works late Friday night and it crosses midnight? One might say it's fine. One might even remember that it'll break on a weekend. But months down the road will you remember? And if a customer walks in the door on a Saturday, what will you do? Tell him that the calc isn't capable of producing the right result because of the day of week? The first time you say something will never happen will be the moment it happens.

Both the calculation Raz produced and the one I produced (but didn't post) are very short but they both break if the date is ever Saturday or Sunday and I think it is important to identify and fix. This calculation works NO MATTER WHAT. I would rather have a longer calculation that works no matter what ... than to have a shorter one that might bite me someday. This calc tests the creation date (and kicks it forward if need be). Then one day added and THAT day tested and kicked forward (if need be). Then the extra day added if after 3PM (and kicked forward if need be).

Let ( [

base = CREATION DATE + ( DayOfWeek ( CREATION DATE ) = 7 ) ;

d1 = base + 1 + Position ( "17" ; DayOfWeek ( base + 1 ) ; 1 ; 1 ) ;

dx = d1 + ( Hour ( CREATION TIME ) ≥ 15) + PatternCount ( "17" ; DayOfWeek ( d1 + ( Hour ( CREATION TIME ) ≥ 15 ) ) )

] ;

dx + Position ( "17" ; DayOfWeek ( dx ) ; 1 ; 1 )

)

I know there are better calculations which will work on all dates. But I could not find it. However, pride can take a flyin' leap - it is more important that it be right on ALL dates (in my opinion).

UPDATE: The original calc of Scott's has two additional calculations in it - which are simply used to create DayOfWeek out of the date. They are unnecessary, resource-wasteful and will slow your calc down. You can eliminate them entirely if using either Raz' calc or this one.

LaRetta :wink2:

Edited June 7, 2006 by Guest
Added update

[Razumovsky]

Good point LaRetta,

I was just trying to simplify the calc so that the OP could weed through it easier. Also, the logic is not completely clear to me on how to handle an entry on a Sat or Sunday - does the "before or after 3" still apply? If the sec waits until the weekend to enter, should the client be penalized (i.e., shouldn't this all run on a "ReceivedDate" instead of creationDate)? Do weekend entries just count as if entered on 12:01 monday morning? I do not know. Assuming the Monday morning scenario, I would go with:

Let([

d = Case(DayOfWeek(CreationDate) = 7; CreationDate +3;

//If created anytime sat, set for tuesday

DayOfWeek(CreationDate) = 1; CreationDate +2;

//If created anytime Sun, set for tuesday

GetAsNumber(CreationTime) ≤ 61200; CreationDate + 1; CreationDate +2)];

//if not sat or sun, set for 1 or 2 days out depending on time created

Case(DayOfWeek(d)=1 or DayOfWeek(d)=7; d+2; d)

)

//Adjust final date to weekday, if needed

It is slightly redundant, but has more transparent logic to me.

-Raz

Edited June 8, 2006 by Guest
added missing parenth

[LaRetta]

Hi Raz,

Yes, there are many possibilities of interpretation; none of the calcs provided are wrong. I went by his original calc with started with a Saturday test kicking to Monday so I thought it might matter to him - point that he takes checks on Saturday but counts Monday as first business day. Half of all businesses are open at least part of a weekend so it would be important that the business rules are clear. And my 35-year business background popped up to assist, pinning down the rules so there are no surprises. Old habit.

I also realized that others use these posts for THEIR solutions and it should be mentioned in case THEIR business works weekends and the distinction might be very important to them.

LaRetta

[Razumovsky]

I see, you actually read through the first calc...

I have to admit, I misread yours at first as well. I kind of like the use of Position (why did you use patterncount for the fourth line?). Nice way to handle a boolean day filter - will definitely come in handy in the future.

Until I get 35 years under my belt, I am afraid I will have to stick with the "May not completely represent your actual business needs" disclaimer. That, and trust the collective wisdom to keep things in line.

-Raz

[LaRetta]

why did you use patterncount for the fourth line

A lazy way to add one day to both Saturday or Sunday without creating two conditional tests.

I didn't invent the use of Position() with dates for skipping days. It was stolen (errr, borrowed) from Comment, -Queue- and Ray, who all have used it in clever and wonderous ways with dates.

Saturday is the only real problem here. If Saturday morning, I assumed Monday would be first business day. If Saturday after 3PM, I assumed Monday would be first business day and Tuesday would be extended business day. Only 1 day needs to be added because Sunday is no problem. Sunday is no problem because adding 1 day is Monday anyway and extended is Tuesday. But each step needs to be kicked past weekend if necessary BEFORE the next test (according to the rules *I* considered which aren't necessarily right).

LaRetta

[Razumovsky]

A lazy way to add one day to both Saturday or Sunday without creating two conditional tests

I guess I mean, why PatternCount here and Position in the other places?

[Søren Dyhr]

A lazy way to add one day to both Saturday or Sunday without creating two conditional tests

Here's another one:

Lenght(Filter(DayOfWeek(d);"146"))

...gives "true" on Sundays or Wednesdays or Fridays!

--sd

[LaRetta]

why PatternCount here and Position in the other places

Position("17") adds 1 to Sunday and 2 to Saturday. PatternCount("17") adds 1 to either Saturday or Sunday. PatternCount() can do something like this: "1117" adds 3 days to Sunday and 1 day to Saturday. Or one can also use Position("701").

So I used PatternCount() here because it needed to jump one day if either Saturday or Sunday and NOT jump 2 days for Saturday and 1 for Sunday. The reason it needs to jump one day if either is because we are testing the SECOND business day (added to d1 which is already a business day) so if the extra day is Saturday, it would jump to Monday.

Oh. All I needed to test on dx variable is Saturday - just as the first variable because we've already ensured that the prior day (d1) is a weekday! Thanks for the catch! We still need to include Position("17") test on dx again though (which I chose to place in the evaluation itself). It could have been included in the dx variable itself but that made it look a bit messy. :wink2: