Calculation that accounts for change in total CRM implemented mid-month

[lizzie]

If

(Created < 1/1/2008 ; Work Days in Month * 5 ; "" );

Greetings

I am going around in circles and feel like a puppy chasing her tail. . .

Our sales consultants had to do 5 CRM calls per day. As of 21/01/2008, the MD upped that total to 10 CRM per day. We are tracking each sales consultant's progress with the aid of a ScoreCard. However, I now have to take into account that the "total required CRM to date" up until 21/01/2008 was "No. of work days past x 5". Thereafter, the calculation changes to ("No. of work days past x 10") + 65 (which is the number of CRM that was to be achieved up until that date).

I will admit I am stuck. I have the logic in my head and have a number of ways of representing it but just cannot get it to calculate accurately.

To get the "number of required CRM to date" i.e. so that the consultants know how many CRM they are ahead/behind, I have the following logical calculation... the issue is that FM tells me that operators are missing. . .

If

(Date CRM Created < 21/01/2008; Work Days in Month * 5);

If

(Created ≥ 21/01/2008 and Created ≤ 31/01/2008; Work Days in Month * 10 + 65) ;

If

(Created > 31/01/2008 ; Work Days in Month * 10 ; "" )

This is an SOS!

Many thanks in advance1

[The Shadow]

You need to use Date(1,21,2008) to create a date, 21/01/2008 is a number divided by another by another that's about .0104 - not probably what you wanted.

[Søren Dyhr]

The calc validation beeps because you have forgotten that each if( opposed to Case( needs 3 things, an 1) equation, 2) a result if true and 3) a result if false.

Unfortunately does nested If( statement's make you loose focus, and you would probably be better off with a Case( statement.

You have put a few parenthesis wrongly to abide to the rules, hence the apparent daft error-message:

This will do:

If(

    created < GetAsDate("20/01/2008";

    workDaysInMonth * 5;

    If(

        created ≥

        GetAsDate("20/01/2008") and created ≤

        GetAsDate("31/01/2008");

        workDaysInMonth * 10 + 65;

        If( created > GetAsDate("31/01/2008"); workDaysInMonth * 10; "" )

    )

) 

However will you functionality probably improve its readability if you do it this way:

Case ( 

created < GetAsDate("20/01/2008");workDaysInMonth*5;

created ≤ GetAsDate("31/01/2008");workDaysInMonth*10+65;

workDaysInMonth*10 )

The calc gives the result for the corresponding calc reached first in the reading direction. But there is a catch to it, could you be sure that the number of workdays means the same in february, so the above only deals with the mechanics of your calc, not if it makes any sense at all.

--sd

Edited January 27, 2008 by Guest
Shawns point taken too!

[comment]

OFF TOPIC:

each if( opposed to Case( needs 3 things, an 1) equation, 2) a result if true and 3) a result if false.

Although the help still doesn't reflect this, 'result if false' is optional since version 7.

[Søren Dyhr]

Ah yes that's correct, but then should the ";" after each end parenthesis be explained, it's like having a bit of both witout going the whole way:

It could be:

Max(If

(created < 21/1/2008; workDaysInMonth * 5);



If

(created ≥ 21/1/2008 and created ≤ 31/1/2008; workDaysInMonth* 10 + 65 );



If

(created > 31/1/2008 ; workDaysInMonth * 10 ; "" ))

But the inefficiency is pretty obvious, every part needs evaluation on it's own where the first occurring match along the line in a Case( doesn't spend unnecessary time with irrelevant calc's!

--sd

[lizzie]

Thank you for the assistance!

I really appreciate the learning curve!

My attempt landed me with the following which works:

Case

(

Created < 733062; Work Days to Date Month Calc * 5;

Created ≥ 733062 and Created ≤ 733072; Work Days to Date Month Calc * 10 - 65;

Created > 733072; Work Days to Date Month Calc * 10;

)

But I think I could certainly improve on thanks to your suggestions!

[Søren Dyhr]

You calc or reasoning still needs some trimming, there is no need for the ranges "in" or "beyond" in your statement.

It could be written this way:

 Case ( 

created < 733062;workDaysInMonth*5;

created ≤ 733072;workDaysInMonth*10+65;

workDaysInMonth*10 ) 

...because each condition it passes in the reading direction, logically imply carries the previous.

Read here to assure yourself of you're doing way too much:

http://www.filemaker.com/help/FunctionsRef-32.html

What you seem to suggest is partly what the C++ Switch does except that here each matching lines actually are executed, for such things do we need a recursive function in filemaker e.g.

http://edoshin.skeletonkey.com/2005/10/number_to_base.html#more

--sd