Calculating a result with multiple Case scenarios

[sboisvert]

I'm not sure this is even possible but I'm hoping for some different direction if it's not. I have written a calculation using Case to add days to a due date based on the type of job they request:

Case (project_eval="New";request_date+28;project_eval="Edit"; request_date+21;project_eval="Reprint"; request_date+18;project_eval="Signage";request_date+28)

This works great as is but I also need to add in 2 other parameter to consider after that:

Case(review="Yes";request_date+14)

And then:

Case (Pages="2";request_date+15;Pages="4";request_date+20;Pages="8";request_date+25;Pages="12";request_date+30;Pages="16+";request_date+35)

So if a person has requested a "New" job that needs "Review" and is "2" pages, the days should equal 57 days.

The Case equation ended after getting the first result (which I believe is what it's supposed to do). So I'm trying to find out if this is the right path to take or should I be using a different calculation all together?

Thanks in advance for your help!!

[LaRetta]

You keep adding these various days to the Request Date when it appears that you want to add each of them to each other and to the request date. I suggest you use Let() to help you clarify what you want. See if this seems closer:

Case ( request_date ;

Let ( [

[color:green]page = Case ( pages = 2 ; 15 ; pages = 4 ; 20 ; pages = 8 ; 25 ; pages = 12 ; 30 ;pages ≥ 16 ; 35) ;

[color:green]jobeval = Case (project_eval="New";28;project_eval=" Edit"; 21;project_eval="Reprint"; 18;project_eval="Signage";28) ;

[color:green]r = Case(Review="Yes";14)

] ;

request_date + [color:green]page + [color:green]jobeval + [color:green]r

) //end let

) //end case

The opening Case() only requires that there be a request date before the calculation evaluates at all. Also notice that I removed the quotes from around the numbers (on numbers of pages etc. Quotes aren't necessary on numbers.

Edited May 18, 2009 by Guest

[sboisvert]

The fact that these selections are Radio Buttons for the user to select should have no bearing on this working correct?

Thank you so much!

[LaRetta]

Radio buttons means that only one value can be entered into the field. So no, it should not cause a problem with the calculation at all. :wink2:

[LaRetta]

What if the number of pages is 3? Might the = be :great: throughout that pages calculation to catch ALL page numbers? Just a thought ...

[sboisvert]

No, the selected page quantities are for print so it needs to be in increments of 4 (aside from 2 pages) but great question! :

Thanks again for your help I will try this and let you know how it works!

[sboisvert]

Ok, I believe I have entered all the information correctly and checked all the "()" that should close everything out but I am getting this warning when I try to complete:

There are more ")" than there are "(" or there are more "(" than there are ")".

Here is the calculation, do you see where I might have gone wrong?

Case ( request_date;Let ( [Pages=Case ( Pages=2;15;Pages=4;20;Pages=8;25;Pages=12;30;Pages="16+";35 );project_eval=Case ( project_eval="New";28;project_eval="Edit";21;project_eval="Reprint";18;project_eval="Signage";28 );lhs_review=Case (lhs_review="Yes";14)];request_date + Pages + project_eval + lhs_review)//end let) // end case

Thanks!

[LaRetta]

You are confusing the specified variable with the field names. And you have changed your field names from your original example. The portion in green (in my prior calculation) was specifying the variable. I was hoping it would be easier to see the calculation if we used Let() and I still think that is true. So notice that the portion in green/bold is a variable and we then refer to the variable in the calculation itself (which is the yellow highlighted portion) at the bottom. I've changed the name of the variables hopefully so they stand out even more different than the field names.

Case ( request_date ;

Let ( [

[color:green]pg = Case ( pages=2;15;pages=4;20;pages=8;25;pages=12;30;[color:brown]pages ≥ 16 ;35 );

[color:green]pjEval = Case ( project_eval="New";28;project_eval="Edit";21;project_eval="Reprint";18;project_eval="Signage";28 );

[color:green]r = Case (lhs_review="Yes";14)

];

request_date + [color:green]pg + [color:green]pjEval + [color:green]r

) //end let

) // end case

Also notice that I again corrected the portion in red. You cannot use = 16+. You must use ≥ (and notice that I again dropped the quotes from around it). Please check the field names as I have entered them. If there is ANY difference in your field names, correct it up in the Let() Portion. The highlighted portion should remain as is.

This should do it for you!

UPDATE: FM Forums changes project_eval="Reprint" by adding an extra space between proje and ct. Note that there should be no space in the middle of that word.

Edited May 19, 2009 by Guest
Added update

[sboisvert]

So the variables do not need to be what the field name is? Perhaps that's where I got confused. Sorry about changing somethings, as I started to actually get the live equation in I used the actual field names.

For the 16+, this is a field using radio buttons where the user will have the option to choose either 2, 4, 8, 12, 16+ pages, maybe it's a better idea to do it the way you have it where they actually enter the number... :

[Fitch]

If you write the case statement this way it should be OK:

Case( pages=2;15;pages=4;20;pages=8;25;pages=12;30;35 )

I.e., if I understood the scenario, 16+ doesn't have to be explicitly defined; its result can be assumed as the default value (35).

[LaRetta]

Hi Tom, I don't think it will work just putting the 35 as the default because then pg 1, pg 3 etc will all revert to 35, if I am correct that there are other pages which should get NO incremented number ... but then again, if it is radio button, there IS no pg. 3 - which was my question earlier! So yes, default result should work great!! Thank you!! I thought it was being said that there are other pages than those listed.

Is Pages a number field or text field? If number field then =16 should be all you need (because it will evaluate without the +). If text field then I believe you will need to wrap it as "16+"

So the variables do not need to be what the field name is?

Correct. In fact, the variables should NOT be the field name. Using a variable is a short way of grouping calculations.

So instead of having one hairy long calculation with all of these pieces in it, we put them as 3 separate variables ... so they are easy to read. Then the calculation itself is simply taking these 3 variables and performing the calculation on THEM instead.

UPDATE: Corrected my response to Tom! And forget about the data type for Pages - use 35 as the default value as Tom suggests.

Edited May 19, 2009 by Guest

[sboisvert]

It works great! Thank you so much for your help, you have no idea how much this is going to fix!!

[sboisvert]

Ok, after review with my team they would like this to have more variables which has proven difficult. This is part of the calculation, which is a modification of the one you supplied. I have no idea if I'm close to having this work but I do know the first Let statement works, it's where the second (and third, etc) ones come in that it stops.

Case ( request_date;

If (project_eval="New";

Let ( [

pg=Case ( Pages=2;5;Pages=4;7;Pages=8;9;Pages=12;11;Pages ≥ 16;13 );

r=Case (lhs_review="Yes";14)

];

request_date + pg + r + 44));

//end case

Case ( request_date;

If (project_eval="Refresh";

Let ( [

pg=Case ( Pages=2;2;Pages=4;2;Pages=8;4;Pages=12;4;Pages ≥ 16;6 );

r=Case (lhs_review="Yes";14)

];

request_date + pg + r + 30))))

Again, this was a guess so I understand if you look at this and go WHAT WAS HE THINKING! :P

[LaRetta]

WHAT WERE YOU THINKING?

Honestly though, I don't quite get the changes.

I can tell now that you want the pages to be different depending upon the Proj_eval. But I see only two types listed - "New" and "Refresh". What about the other types of "Edit, "Reprint" and "Signage?"

It seems that, regardless of the proj_eval type, 14 should be added if lhs_review = "Yes"

Then you've thrown me a bit by: within the first calculation for "new" adding 44 and within "Refresh" adding 30.

Bottom line, your calculation won't work (as you are attempting) and we will need to adjust it for you; no prob. Forget about how to structure the calc for now and just give me the new rules, please, and explain what that 44 and 30 represent that are added onto the end. :wink2:

[Fitch]

I'd write it more like this:

Case ( request_date;

  Let ( [

       p = Pages ; 

       eval = project_eval ; 

       pg = 

         Case( eval="New"; Case ( p=2;5;p=4;7;p=8;9;p=12;11;p ≥ 16;13 ) ;

               eval="Refresh"; Case ( p=2;2;p=4;2;p=8;4;p=12;4;p ≥ 16;6 ) );

       r = Case( lhs_review = "Yes"; 14 ) ; 

       x = Case( eval="New"; 44; eval="Refresh"; 30 )

       ];



       request_date + pg + r + x 

    )  // end LET

   )  // end CASE

[sboisvert]

Yes, you are right, I did leave out the other scenarios, my apologizes

I was trying to get these 2 to work then I figured I could figure out the rest.

Ok, so based on an internal discussion we have decided to use the following project_evals, these will have a predetermined number assigned to them automatically:

New = 44

Refresh (this replaced "Edit" which I had before) = 30

Reprint = 19

Signage = 19

Reprint and Signage will only be 19, page count is 0 regardless and you are correct about the lhs_review, it will always be 14 if "Yes".

The page count, based on if it's New or Refresh, will change, but will remain zero for Reprint and Signage. Does that make sense?

Here is the equation as I originally started working on it, and again, probably horribly wrong but I believe the premise is there.

Thanks again for all your help!!!

Case ( request_date;

If (project_eval="New";

Let ( [

pg=Case ( Pages=2;5;Pages=4;7;Pages=8;9;Pages=12;11;Pages = 16;13 );

r=Case (lhs_review="Yes";14)

];

request_date + pg + r + 44));

If (project_eval="Refresh";

Let ( [

pg=Case ( Pages=2;2;Pages=4;2;Pages=8;4;Pages=12;4;Pages = 16;6 );

r=Case (lhs_review="Yes";14)

];

request_date + pg + r + 30));

If (project_eval="Reprint";

Let ( [

pg=Case ( Pages=2;0;Pages=4;0;Pages=8;0;Pages=12;0;Pages = 16;0 );

r=Case (lhs_review="Yes";14)

];

request_date + pg + r + 19));

If (project_eval="Signage";

Let ( [

pg=Case ( Pages=2;0;Pages=4;0;Pages=8;0;Pages=12;0;Pages = 16;0 );

r=Case (lhs_review="Yes";14)

];

request_date + pg + r + 19))

)//end if

//end let

// end case

[LaRetta]

Alright, then Tom's calculation will need small adjustment. I have changed the 'accumulated' number for each type of Eval to x, indicating combining the page numbers and the predetermined number for each. "Reprint" and "Signage" don't have page numbers and use the same predetermined number but I listed them separately (and listed pages as 0) so that, when you view this calculation in the future, there will be no question as to how each eval is handled. See if this fits for you - we can keep adjusting if needed:

Case ( request_date ;

  Let ( [

       p = pages ; 

       eval = project_eval ; 

       x = 

         Case ( eval = "New" ; 44 + Case ( p=2;5;p=4;7;p=8;9;p=12;11;p ≥ 16;13 ) ;

               eval = "Refresh" ; 30 + Case ( p=2;2;p=4;2;p=8;4;p=12;4;p ≥ 16;6 ) ;

               eval = "Reprint" ; 19 + 0 ; 

               eval = "Signage" ; 19 + 0 ) ;  

       r = Case ( lhs_review = "Yes" ; 14 )  

       

       ] ;



       request_date + x + r 

    )  // end LET

   )  // end CASE 

LaRetta

[sboisvert]

That's awesome!

As per usual, they have changed one small thing on me (I say small but we'll see )

They would like the Refresh pages to have a separate page count list value. This is becuase the want to know how many pages the piece is, say it's 12 pages, but only 2 of those pages need to be revised. So I have added refresh_pages to the calculation. Were as the "Pages" for a Refresh will still be checked off, they will have no value like "Signage" and Reprint". It will have values assigned to it by how many refresh_pages there are.

This is how I have modified it based on yours, do you see anything glaring? Also, do I need to have "// no pages on Reprint or Signage" in the calculation?

Case ( request_date ;

Let ( [

p = pages ;

eval = project_eval ;

rp = refresh_pages;

x =

Case( eval = "New" ; 44 + Case ( p=2;5;p=4;7;p=8;9;p=12;11;p ≥ 16;13 );

eval = "Refresh"; 30 + Case ( rp=2;2;p=4;2;p=8;4;p=12;4;p ≥ 16;6 );

eval = "Reprint"; 19 ;

eval = "Signage"; 19 ) ; // no pages on Reprint or Signage

r = Case( lhs_review = "Yes" ; 14 )

] ;

request_date + x + r + rp

)// end LET

)// end CASE

Thanks again for your INCREDIBLE patience and help!!

[LaRetta]

I'm a bit unsure on the logic of how you are using refresh_pages but I see that you are *getting* how we are using Let() to clarify the calculation. I would question how you have rp on the "Refresh" eval and also again down in the actual calculation itself (adding it to the end). In fact, a large part of me wonders if there isn't a better way of accomplishing whatever it is you are accomplishing but I won't go there. Let's just get you set up with this calc ...

Your calc seems fine if the logic with the rp is correct. Keep in mind that, if you include the rp in the "Refresh" eval AND have it down in the calculation, it will add it again (which may be okay).

As for the // no pages on Reprint or Signage ...

I had changed the calc to:

eval = "Reprint"; 19 + 0 ;

eval = "Signage"; 19 + 0 ) ;

... I changed it because I just didn't like the way the CODE changed the color of the remaining lines of the calculation to orange. Ha ha ha. I think it is important to somehow designate that pages are 0. It is your calculation and you can notate it as you wish so it is clear to you. This is the beauty of using Let() statements to clarify and using // comments to help you remember.

UPDATE: I was just given a raised eyebrow for using 19 + 0. I originally had both those lines with a comment [color:green]// no pages. I shouldn't have changed it just because it was ugly with the colors in my code since it DOES waste two evaluations.

LaRetta

Edited May 28, 2009 by Guest

[sboisvert]

Cool , I didn't realize they could be used that way!

Let me try to explain the Refresh pages. If you have a brochure that is 16 pages but you want 4 pages to be re-designed it will take 2 days. We would still need to know how many pages it is but all 16 are not New like in a "New" job. Does that make sense?

I would be totally open to any other suggestions you have. I am concerned though about it adding it again! So I should remove rp from the calculation at the bottom? Are you saying that it could possibly add "Pages" quantities to it?

Not sure if I made the refresh_pages field clear. I need to include this so that we can still see how many pages the document is but we really need to know is how many of those pages will need revisions.

Edited May 28, 2009 by Guest

[LaRetta]

Let me try to explain the Refresh pages. If you have a brochure that is 16 pages but you want 4 pages to be re-designed it will take 2 days. We would still need to know how many pages it is but all 16 are not New like in a "New" job. Does that make sense?

As to whether the rp is properly handled within the calc ... I am unsure if you understand how it will produce results within the Case() calculation when the rp only is addressing the first test in "Refresh" as rp=2;2. Do you understand how that Case() calculation will work? If not, you might create it by itself and check your results. I would suggest two things at this point:

1) Create a test file with only these fields and every combination of values and test, test, test to be sure your results are addressing all possibilities properly and 2) if you wish to consider another approach, go to Relationships forum and create a brand new post asking that people here consider your structure. Attach a zipped, empty clone of your file (or post a demo file) showing enough sample data and provide an overview of your business practice.

I always worry when I see this much hard-coding and many exceptions within a calc. Possible example of good structure (one project with many related 'actions':

Project Table: Project #1:

Actions Table:

Project #1 - 5/25/2009 Project_eval "New" = 16 pages

Project #1 - 5/26/2009 Project_Eval "Refresh" = 2 pages

Project #1 - 5/27/2009 Project_Eval "Reprint" = 4 pages

... it just feels that you are attempting to handle within a calculation multiple 'actions' which might be better as related records (actions).

[sboisvert]

Ok, I've attached a Test file for you to look at. It seems to work, however the numbers for Pages and refresh_pages are being added even with a New eval. Is that because of the equation at the end?

Project_Test.zip

[LaRetta]

RP will be added to EVERYTHING because it is appearing down in the actual calculation of:

request_date + x + r + rp

... and that was my concern. So if you want rp to only apply to "Refresh" then it should only be included in that particular Case() calculation. It is THAT portion of the calculation that I'm unclear on:

eval = "Refresh"; 30 + Case ( [color:blue]rp=2;2;p=4;2;p=8;4;p=12;4;p ≥ 16;6 );

This is saying that, if rp = 2, count 2 pages. But the rest of the tests within that Case() only apply according to the number of pages. Is this what you want? What are the exact rules on rp? When is it added in and when not?

UPDATE: Could you possibly be saying to add rp to "Refresh" ... in addition to the pages portion? If so then that line would be:

eval = "Refresh"; 30 + Case ( p=2;2;p=4;2;p=8;4;p=12;4;p ≥ 16;6 ) [color:blue]+ rp ;

Edited May 28, 2009 by Guest

[sboisvert]

Once again, thank you so much for your guidance and patience, you're awesome

The rp only applies to a "Refresh" project eval. The Pages numbers do not apply to Refresh only how many refresh pages. By the way, the numbers associated to all of these things are days. We are trying to let people know based on their selections how many days until it's ready from their request date.

So really pages would only apply to New, refresh_pages to Refresh, and the Reprint and Signage have their own fixed dates.

Do you still think I'm trying too many variables in this equation? It appears that we are very close though!! :laugh2:

[LaRetta]

The rp only applies to a "Refresh" project eval. The Pages numbers do not apply to Refresh only how many refresh pages.

Then if I understand correctly, this line:

eval = "Refresh"; 30 + Case ( [color:blue]rp=2;2;p=4;2;p=8;4;p=12;4;p ≥ 16;6 );

should be changed to (because you are only basing the pages on the rp field)

eval = "Refresh"; 30 + Case ( [color:blue]rp=2;2;[color:blue]rp=4;2;[color:blue]rp=8;4;[color:blue]rp=12;4;[color:blue]rp= 16;6 );

... and it should be dropped from the main calculation below.

[sboisvert]

YES!!!

Once I looked the equation after your last post I got it! I was wondering why selecting different "Pages" values were effecting the Refresh results. Obviously because after the first rp+2;2 it went p=4;2.

You are awesome, I think the problems been solved! I will have to test it a bit more but I think it's there. Thank you so much! :yourock:

[sboisvert]

I'm sorry but I have one other question pertaining to this post.

The calculation works great but now I'm trying to get it to do the reverse thing. I want them to supply me with their available_date and it will generate a date that they need to submit.

I have the Submit_date calculation like this but it is still calculating the information forward instead of backwards. I belive I'm asking it to do this in the wrong way with the minus operator but can't figure it out why?

Case ( available_date ;

Let ( [

p =pages ;

eval =project_eval ;

rp = refresh_pages;

x =

Case( eval = "New" ; - 44 + Case ( p="2";-5;p="4";-7;p="8";-9;p="12";-11;p ≥ "16+";-13 );

eval = "Refresh"; -30 + Case ( rp=1;-2; rp=2;-2; rp=3;-2; rp=4;-2; rp=5;-4; rp=6;-4; rp=7;-4;rp=8;-4; rp=9;-4; rp=10;-4; rp=11;-4;rp=12;-4;rp ≥ "13+";-6 );

eval = "Reprint"; -19 ;

eval = "Signage"; -19 ) ;

r = Case (lhs_review = "Yes" ; -14 )

] ;

available_date-x-r

)// end LET

)// end CASE

Thanks again!!!

[LaRetta]

This is the number of days which should be adjusted ... increased if we are looking forward to find the Available_Date (being given the request_date) or decreased if we are looking backward from the available_date to find the suggested submit_date). But the NUMBER OF DAYS it will take to do the job doesn't change and this number is represented with the Let() statement as grey! You should NOT adjust the calculation within the Let() at all!

So if you are given the request_date (presumably the current date), you ADD the grey portion number to get the date it will be available. And if you are given the date when it should be available, you would reverse and subtract the grey number result. So all that would change from your original calculation is the portion in blue:

Case ( [color:blue]available_date ;

[color:gray]Let ( [

p = pages ;

eval =project_eval ;

rp = refresh_pages;

x =

Case( eval = "New" ; 44 + Case ( p=2;5;p=4;7;p=8;9;p=12;11;p ≥ 16;13 );

eval = "Refresh"; 30 + Case ( rp=2;2;rp=4;2;rp=8;4;rp=12;4;rp= 16;6 );

eval = "Reprint"; 19 ;

eval = "Signage"; 19 ) ;

r = Case( lhs_review = "Yes" ; 14 )

] ;[color:blue]available_date [color:blue]- x - r

)// end LET

)// end CASE

Example: If the Customer wants it available on 8/5/2009, and according to the numbering criteria it will take 63 days to complete the job, then the submit date would be 6/3/2009.

Edited June 1, 2009 by Guest

[sboisvert]

Ugh of course!!

Thank you, thank you, thank you....

I still hope there are people out there who think "There are no stupid questions..."

You're the best!

[LaRetta]

"I still hope there are people out there who think "There are no stupid questions..."

I happen to be one who thinks it and I say it quite frequently. Not to worry...