Quarterly calculations

[rivet]

I need to produce a reports from two types of searches.

1. Choose all or one of the quarters and select a year.

2. Choose start month and year and then an end month

From the two choices I need to calculate a search string (i.e. 9/1/2003...6/1/2004)

With both I need to find a formula that can determine if end month or the quarter month is in the same year as the first quarter. Also how do I keep the month in a scale of 12.

Choice 1

-fiscal start is Sep (09)

-report for 2nd quarter

-year is 2003

How do you find second quarter (12,1,2) and determine that the last month is in a new year.

Choice 2

-fiscal start is Sep (09)

-report from sep 2003 to march

-so the month is obvious 9...3 but how about the year. Months 9-12 stay in the same year but 1-8 add one to the selected year.

I attached a file that might show things better.

[Lee Smith]

Hi rivet,

This technical article by FileMaker may be of help to you.

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

HTH

Lee

[Ugo DI LUCA]

Hi Rivet,

How do you find second quarter (12,1,2)

Use the Mod function here. If fiscal start is september (09),

First quarter = MOD(INT((("your month in number"+3) / 3) ) ;4)+1

Second quarter = MOD((MOD(INT((("your month in number"+3) / 3)) ;4)+1);4)+1

Third quarter = MOD(MOD((MOD(INT((("your month in number"+3) / 3)) ;4)+1);4)+1;4)+1

Fourth quarter = MOD(MOD(MOD((MOD(ENT((("your month in number"+3) / 3)) ;4)+1);4)+1;4)+1;4)+1

and determine that the last month is in a new year.

You could use another calculation to return the calendar quarter (not fiscal).

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

Therefore, use a Case calc :

Case(Calendar Quarter starting date > Calendar Quarter end date; Year("your month in number") +1; Year("your month in number"))

Note that all these calcs would have been possible using the Month(Date field) instead of "your month in number"...

[BobWeaver]

Fortunately, Filemaker's Date function can do a lot of this automatically.

Given that you have:

- a start month for your fiscal year (let's call it nStartMonth),

- A starting quarter (nStartQuarter) for your report,

- An ending quarter (nEndQuarter) for your report,

- and finally the year (nYear)

These fields are all number type (not date)

Then your report dates are:

ReportStartDate = Date(nStartMonth+(nStartQuarter-1)*3,1,nYear)

ReportEndDate = Date(nStartMonth+nEndQuarter*3,0,nYear)

These calculations have a date result, and the month and year will automatically roll over into the new year correctly.

So, finally, your search string will be:

DateToText(ReportStartDate) & "..." & DateToText(ReportEndDate)

[Ugo DI LUCA]

Hi Bob,

There wasn't any endquarter in Rivet Post nor Attachment.

His calcs needed a mix of calendar and fiscal quarters and months, but I'm quite sure he can fix it now with this easy 2 lines calculations.

[rivet]

Thanks so much guys... you help keep my hair in and the family calm.

[BobWeaver]

Ugo, I included an end quarter field in order to make the calculation more general. If the report is for a single quarter, then the end quarter is equal to the start quarter. If the report is for a whole year, then the end quarter is equal to start quarter + 3, etc.

And of course to get the month number of any date (such as the fiscal year start date) it's just a matter of using the Month(SomeDateField) function.

Also, something I didn't mention is that nYear is the year in which the period begins (in case the report period spills over to the next year).

I do tend to skip over the details sometimes.

[rivet]

Bob, How about a month to month report? How do I find the end month and have the year advance if necessary?

startmonth=6, endMonth=7, year would stay the same

startmonth=6, endMonth=3, year would advance one

s=3 e=2, year would advance

I first assumed that if e/s>0, I could add that 1 to the year. but 'FAIL'

Ultimately I know it should be similar to your above solution, and just add to the month, but how do...

Just figured it out - I think...

DateDateToText(startMonth,1,_ReportYear))

& "..." &

DateToText(Date(endMonth+(endMonth/startMonth>0)*12,0,_ReportYear))

No again 'FAIL'

[Ugo DI LUCA]

While waiting for Bob,

from my former answer :

and determine that the last month is in a new year.

You could use another calculation to return the calendar quarter (not fiscal).

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

Therefore, use a Case calc

Case(Calendar Quarter starting date > Calendar Quarter end date; Year("your month in number") +1; Year("your month in number"))

[Lee Smith]

I wish that the internal quoting worked so one could actually read the quote?

Lee

[BobWeaver]

I see a problem with your formula. (endMonth / startMonth>0) will always be true. I think you may have intended to use (endMonth / startMonth>1), or else (endMonth - startMonth>0), either of which should work.