Brian Dunning's FileMaker Custom Functions

FiscalQuarter ( date ; startingMonth ; startingDay ; nameByEnd )

Returns the fiscal year and quarter in which date occurs.

  Average rating: 4.2 (36 votes) Log in to vote

Michael Horak   Michael Horak
*COMMENT Visual Realisation
http://comment.cjb.net/

Share on Facebook Share on Twitter

  Sample input:
FiscalQuarter ( 12/14/2013 ; 9 ; 15 ; 0 )

FiscalQuarter ( 12/15/2013 ; 9 ; 15 ; 0 )

FiscalQuarter ( 12/15/2013 ; 9 ; 15 ; 1 )


(sample dates are shown using mm/dd/yyyy format)
  Sample output:
2013Q1

2013Q2

2014Q2

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Returns the fiscal year and quarter in which date occurs.

The function parameters are:
date - any calendar date;
startingMonth - the month in which the fiscal year starts;
startingDay - the day of month on which the fiscal year starts;
nameByEnd - a Boolean parameter for naming the fiscal year; when nameByEnd is false, the fiscal year's name will be the calendar year in which the fiscal year starts; when nameByEnd is true, the fiscal year's name will be the calendar year in which the fiscal year ends.

This function is NOT recursive.

 

Comments

Austin Burbridge   Austin Burbridge, Los Angeles
Mar 14, 2014
Parameter `date` is a reserved word in Filemaker Pro. The code should be changed to accomodate that, substituting a non-reserved word for the parameter, for example, `anyDate` :

Let ( [
m = Month ( anyDate ) - ( Day ( anyDate ) < startingDay ) ;
q = Div ( Mod ( m - startingMonth ; 12 ) ; 3 ) + 1 ;

startingYear = Year ( anyDate ) - ( m < startingMonth ) ;
endingYear = Year ( Date ( startingMonth ; startingDay - 1 ; startingYear + 1 ) ) ;
y = Case ( nameByEnd ; endingYear ; startingYear )
] ;
y & "Q" & q
)
 
comment   comment, VR
Mar 15, 2014
@Austin Burbridge:

Thank you for your comment. You are partially correct.

First, "date" is NOT a reserved word in Filemaker. You can name your field "date" and use it in any calculation formula with no problems. You can name any type of variable - including a Let() variable - "date" and it will work flawlessly.

However, when constructing a custom function, you are prevented from using "date" (or any other function name) as the function parameter name. I don't see any good reason for this, given the above. Therefore I knowingly and purposefully ignore this limitation and give my functions proper parameter names, same as FMI give theirs (cf. the Day() function, for example). Consider it a political statement, if you will.

Hopefully FMI will come to their senses one day and change their policy (if it even is a policy and not a mere oversight). Then this function will work as written. Until then, you and others wishing to take advantage of it will have to endure a small inconvenience. I suggest you write to FMI about it.
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.