Help with Calc please?

[Ronni]

I have the following calculation on a field to automatically display to the Sales team which quarter we are in.

However, it doesn't work.

I can't for the life of me figure out why. The syntax takes on the field calculation, but doesn't display anything.

I am using Filemaker 5.5 on the Windows 2000 Professional Platform.

Now is just the function TODAY so I can display today's date in the header of the sales form.

Case(Now < 3/30/2001, "Q1",

Now > 3/30/2001 and Now <= 6/30/2001, "Q2",

Now > 6/30/2001 and Now <= 9/30/2001, "Q3",

Now > 9/30/2001 and Now <= 12/31/2001, "Q4")

I have the output set for Text. I'm baffled.

Help Please?

Ronni.

[Moon]

Try using status(currentdate) function instead of Now field, and set the field to unstored.

You can display the current date in the header without using a calc field by using the metacharacter //.

[Ronni]

Thank you for your reply... however...I'm confuzzled now even more

I can't use currentdate in my calc as I have the calc written. And I don't know of a method where CASE allows you to compare values without setting a field for comparrison to set results. I need to be able to:

Designate when Quarters start and end (they will be shifting once we get the system into place)

Have that information stored in a field to be both used in other calculations and displaying in reports, and data entry decisions.

Do a comparrision of today's date to the established set of when does each quarter start and end.

I'm using the results of this field (Current Quarter) in many different other calcs to handle forcasting and task assignments with the sales tool system.

Here I thought I actually started to understand this...

Sigh.

[Moon]

quote:

---

Originally posted by Tai-Kitty:

Thank you for your reply... however...I'm confuzzled now even more

I can't use currentdate in my calc as I have the calc written. And I don't know of a method where CASE allows you to compare values without setting a field for comparrison to set results.

---

I'm not sure what you mean by "can't use current date as you have the calc written.

You CAN compare values without setting a field to compare. Your original calculation could have substituted either the Today function or the Status(CurrentDate) function for the field "Now" to the same effect, which is to compare today's date to date ranges entered as constants. Case and If statements can compare constants, fields, results of calculations or the result of functions without having to have fields defined first. Your Now field is redundant, because you can display the current date using the metacharacter as I mentioned earlier, and can use either the Today function or the Status(CurrentDate) function directly in your calculations.

The Today function evaluates once, each time you start the database, which is a major disadvantage on solutions which are run continuously on a server. If you use status functions in defined calculation fields, the calculation results must be unstored or the calculation will not update when the status changes. Setting a field as unstored has one disadvantage in that the field cannot be indexed, and calculation fields using that field likewise cannot be indexed, which can slow searches and sorts on large databases.

HOWEVER, none of this explains why your calc does not evaluate, and my earlier post didn't answer it either. Here is what is going on.

Your current Case statement is evaluating your Now field against numbers that are dividing and dividing, instead of evaluating against dates. Here's what I mean. FileMaker is evaluating expressions such as 3/30/2001 as three divided by thirty divided by 2001, and the result is very tiny. No date I know of is so small, so the whole calculation evaluates to null.

Two solutions come to mind. Either substitute for your date expressions the term TextToDate(your date) or use the Date() function for each date expression; for example Date(3,30,2001). Better yet, use Date(3,30,Status(Currentyear) and you have a calculation you can use next year, too. One more thing: Make all four quarters have a lower and upper bound defined, and watch your greater than/less than and equal statements. All lower bounds should be greater than or equal to the beginning date of the quarter, and upper bounds should less than or equal to the last day of the quarter. I notice in your example that Q1 and Q2 exclude the date 3/30/2001 because you did not use a less than symbol in the Q1 upper bound.

Hope this helps more than my last ill-conceived reply.

[ August 14, 2001: Message edited by: Moon Mullins ]

[billvision]

Try this...

Case(Status(CurrentDate) < Date(3,30,Status(Currentyear), "Q1",

Status(CurrentDate) > Date(3,30,Status(Currentyear) and Status(CurrentDate) <= Date(6,30,Status(Currentyear), "Q2",

Status(CurrentDate) > Date(6,30,Status(Currentyear) and Status(CurrentDate) <= Date(9,30,Status(Currentyear), "Q3",

Status(CurrentDate) > Date(9,30,Status(Currentyear) and Status(CurrentDate) <= Date(12,30,Status(Currentyear), "Q4")

[bobsmith]

I find it easier to use the choose function when dealing with quarters. I gives you a shorter calculation and is flexable in case the first quarter does not start in January.

Choose(Month(Status( CurrentDate)),"", "Qtr 1","Qtr 1","Qtr 1","Qtr 2","Qtr 2","Qtr 2","Qtr 3","Qtr 3","Qtr 3","Qtr 4","Qtr 4","Qtr 4")

[Kundinger]

I agree with 'bobsmith':

Choose(Month(Status(CurrentDate)),"", "Qtr 1","Qtr 1","Qtr 1","Qtr 2","Qtr 2","Qtr 2","Qtr 3","Qtr 3","Qtr 3","Qtr 4","Qtr 4","Qtr 4")

This works very well in an organization with Jan/Feb/Mar as "Qtr 1".

In situations where the 'fiscal year' has different quarters, you will need to alter the above formula.

IDEA:

Use a "Preferences" file with fields that will contain Month & Quarter settings. Create a "Lookup" relationship to 'capture' the proper quarter. This allows your user (with proper priviledges) to make the change without having access to 'Define Fields'.

Good Luck!

Bob

[Vaughan]

Why not just change the Date Format of the field to display as a quarter, ie, Q1 etc? No need for a calculation field.

[Kundinger]

Hi Vaughan,

Your right... I should have remembered the date 'format' options. It does allow your to use 'quarters'.

I remember now why I don't use it...

The business fiscal year for some of my clients start at various time throughout the year. That is why I use a 'prefs' file to hold that type of info.

Thanks for reminding me though.

Bob

[bobsmith]

Yes I forgot also for the same reason as Bob, our FY begins in July so Choose works better for me.

[RussBaker]

Why not use 4 global date fields to store the end dates for the quarters. Then you can use expressions like Status(CurrentDate) <= g_Qtr1,"Q1" in your case statement. This would allow you to alter the quarter dates as required. You could set the global quarter date fields by either a lookup from a preferences file on startup, SetField script steps on startup, or user input (risky). Using either of the starup options would allow you to check for the group the user belonged to and perhaps allocate a different quarter depending on who they were. ie, the aqccountants could get fiscal year quarters and the rest could have calendar year quarters...

[bobsmith]

I guess we are beating this one to death but I still would like to use the Choose function. This could also be done with a calculation:

Case (Status ( CurrentGroups )=1,

Choose(Month(Status( CurrentDate)),"", "Qtr 1","Qtr 1","Qtr 1","Qtr 2","Qtr 2","Qtr 2","Qtr 3","Qtr 3","Qtr 3","Qtr 4","Qtr 4","Qtr 4"),

Choose(Month(Status( CurrentDate)),"","Qtr 3","Qtr 3","Qtr 3","Qtr 4","Qtr 4","Qtr 4", "Qtr 1","Qtr 1","Qtr 1","Qtr 2","Qtr 2","Qtr 2")

Where User group 1 uses calendar quarters and all other groups use fiscal quarters where the fiscal year begins in July.