Case - converting date ranges to text value?

[Bob476]

I have a series of records with sale dates. What I want to do is create a new text field that will give the Quarter for each sale. So far, I figure I need to use some kind of nested Case function for the text calculation, but have not been able to figure this out.

The test I want to do is if the SALEDATE falls in a range of dates then SALEQUARTER equals "quartername" and the cascade down nested cases until most recent quarter. Not sure whether my problem is in not knowing how to test date ranges or how to nest the cases.

[Ugo DI LUCA]

Hi,

Yessssss!

I finally got the choose function to work.

Calendar Quarter = Int(("your month in number" +2)/3) will return the calendar quarter of that date.

Your month in number = Month(Your date field)

Choose function :

Choose(Calendar Quarter,"","first quarter","second quarter","third quarter","fourth quarter")

[Bob476]

Um, okay. Not sure how to relate that to my goal. I'm looking to convert date ranges to specific custom text values, like "2001-Q1, 2001-Q2," and so on.

Also, I should have mentioned that I'm not looking for "calendar" quarters. Jan-Mar, for example, would be Q4, and Apr-Jun would be Q2.

[Ugo DI LUCA]

I was so happy with that choose tip

What's up with your quarters !

FM can solve the "logical" calcs only , but you can give "him" steps to interpret your logic.

So use the Case function here, with your own quarters, keeping the Month(Date) to determine the month number.

Assuming "2001" is your year date

Then why not again a choose function with :

calendarquarter = Choose (Month(Date), "" , "Q4 & "-" & Year(Date)"; ...

where you're going 0,1,2,3,4,5,6,7,8,9,10,11,12 in the choose results.

"Q4 & "-" & Year(Date)" is the result for 1 (january)

"Q4 & "-" & Year(Date)" is the result for 2

"Q4 & "-" & Year(Date)" is the result for 3

"Q2 & "-" & Year(Date)" is the result for 4 (april)

....

[cjaeger]

The calc is:

if(Mod(Round(month/3 + q1start_month*1/3;0);4);Mod(Round(month/3 + q1start_month*1/3;0);4);4)

(substitute the ";" for "," if using US-FileMaker)

use 1/3 (0.333) (because 3 months make a Quarter, 4 because Quarters repeat after 4)

[Ugo DI LUCA]

Hi,

Your start_month is pretty slick, but how do you determine without a case that the next quarter after 4 is 2

I'll give your calc a try. Thanks.

[Lee Smith]

Hi Bob,

FileMaker has a tech artical about this that you might also want to read. Here is ther URL

http://www.filemaker.com/ti/101688.html

HTH

Lee

[Bob476]

Thanks!

In the end, I split quarter and year into two fields.

For fiscal quarter I used the choose function.

For fiscal year, I just used a nested IF and used number values to test the dates, like so:

If(start date<729845,"1999", If(start date<730211,"2000", If(start date<730576,"2001", If(start date<730941,"2002",If(start date<731306,"2003",If(start date<731672,"2004",If(start date<732037,"2005", "Out of range")))))))

This assumes 1999 is first possible year.