Last and next fiscal year quarter

[Glorifindal]

Hi there,

so, I worked out how to do a Year Quarter, based on a project date ending, when my boss turned and said - err, I meant Fiscal Year.

Which leaves me with working out how to show which quarter a date is in - over 3 years , last year, current year, next year

For example, the date 26/03/2011 is in the last quarter of last year,

The data I am producing is in the format (Jan...Dec Year):

Project 1 Q1 (date = 26/03/2011)

Project 2 Q3 (date = 10/08/2011)

What I need to produce is ( UK, Fiscal year start to Fiscal year end) :

Project 1 Q410 (date = 26/03/2011)

Project 2 Q311 (date = 10/08/2011)

Project 3 Q212 (date = 29/04/2012)

Anyone have an idea as to how this can be accomplished? I am at a loss really

Many thanks in advance

Glorifindal

[Glorifindal]

Hi there,

so, I worked out how to do a Year Quarter, based on a project date ending, when my boss turned and said - err, I meant Fiscal Year.

Which leaves me with working out how to show which quarter a date is in - over 3 years , last year, current year, next year

For example, the date 26/03/2011 is in the last quarter of last year,

The data I am producing is in the format (Jan...Dec Year):

Project 1 Q1 (date = 26/03/2011)

Project 2 Q3 (date = 10/08/2011)

What I need to produce is ( UK, Fiscal year start to Fiscal year end) :

Project 1 Q410 (date = 26/03/2011)

Project 2 Q311 (date = 10/08/2011)

Project 3 Q212 (date = 29/04/2012)

Anyone have an idea as to how this can be accomplished? I am at a loss really

Many thanks in advance

Glorifindal

So, I got so far - which I think works now anyway -

"Q" and Int(( Mod ( dateEnd ) + ( 12 - 4 ) ; 12 ) +1 +2 ) / 3 ) & Right(Year( dEnd ) - (Month ( dEnd ) < 4 ;2)

Not to elegant but it seems to work.

The 4 in the above is April, our Fiscal year

If someone can improve on this, or see a bug in it, please let me know,

kindest

Glorifindal

[comment]

"Q" and Int(( Mod ( dateEnd ) + ( 12 - 4 ) ; 12 ) +1 +2 ) / 3 ) & Right(Year( dEnd ) - (Month ( dEnd ) < 4 ;2)

Not to elegant but it seems to work.

I doubt that very much.

[bruceR]

Then perhaps you should then be making calculations based on the native FileMaker fiscal year date function:

WeekOfYearFiscal

Purpose:

Returns a number between 1 and 53 representing the week containing date, figured according to startingDay.

Format:

WeekOfYearFiscal(date;startingDay)

http://fmhelp.filemaker.com/fmphelp_11/en/html/func_ref1.31.23.html#1028831

[comment]

Then perhaps you should then be making calculations based on the native FileMaker fiscal year date function:

WeekOfYearFiscal

I don't see how WeekOfYearFiscal() can help here. However, since this seems to be for display only, it could be done quite simply:

DisplayFQ.zip