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 - Show more from this author
*COMMENT Visual Realisation |
FiscalQuarter ( 12/15/2013 ; 9 ; 15 ; 0 )
FiscalQuarter ( 12/15/2013 ; 9 ; 15 ; 1 )
(sample dates are shown using mm/dd/yyyy format)
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, 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, 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. |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.