FIYYQ: Converting a Calendar Date to a Financial Date

Syntax: How to Convert a Calendar Date to a Financial Date

FIYYQ(inputdate, lowcomponent, startmonth, startday, yrnumbering, output)

where:

inputdate

Date

Is the date for which the financial year is returned. The date must be a standard date stored as an offset from the base date.

If the financial year does not begin on the first day of a month, the date must have Y(Y), M, and D components, or Y(Y) and JUL components (note that JUL is equivalent to YJUL). Otherwise, the date only needs Y(Y) and M components or Y(Y) and Q components.

lowcomponent

Alphanumeric

Is one of the following:

D if the date contains a D or JUL component.
M if the date contains an M component, but no D component.
Q if the date contains a Q component.

startmonth

Numeric

1 through 12 are used to represent the starting month of the financial year, where 1 represents January and 12 represents December. If the low component is Q, the start month must be 1, 4, 7, or 10.

startday

Numeric

Is the starting day of the starting month, usually 1. If the low component is M or Q, 1 is required.

yrnumbering

Alphanumeric

Valid values are:

FYE to specify the Financial Year Ending convention. The financial year number is the calendar year of the ending date of the financial year. For example, when the financial year starts on October 1, 2008, the date 2008 November 1 is in FY 2009 Q1 because that date is in the financial year that ends on 2009 September 30.

FYS to specify the Financial Year Starting convention. The financial year number is the calendar year of the starting date of the financial year. For example, when the financial year starts on April 6, 2008, the date 2008 July 6 is in FY 2008 Q2 because that date is in the financial year that starts on 2008 April 6.

output

Y[Y]Q or QY[Y]

In case of an error, zero is returned.

Note: February 29 cannot be used as a start day for a financial year.

Example: Converting a Calendar Date to a Financial Date

The following request against the CENTHR data source converts each employee starting date (field START_DATE, format YYMD) to a financial date containing year and quarter components in all the supported formats: YQ, YYQ, QY, and QYY.

DEFINE FILE CENTHR
FISYQ/YQ=FIYYQ(START_DATE,'D',10,1,'FYE',FISYQ);
FISYYQ/YYQ=FIYYQ(START_DATE,'D',10,1,'FYE',FISYYQ);
FISQY/QY=FIYYQ(START_DATE,'D',10,1,'FYE',FISQY);
FISQYY/QYY=FIYYQ(START_DATE,'D',10,1,'FYE',FISQYY);
END
TABLE FILE CENTHR
PRINT START_DATE FISYQ FISYYQ FISQY FISQYY
BY LNAME BY FNAME
WHERE LNAME LIKE 'C%'
END

On the output, note that the date November 12, 1998 (1998/11/12) is converted to Q1 1999 because the starting month is October (10), and the FYE numbering convention is used:

Last            First         Starting
Name            Name          Date        FISYQ  FISYYQ   FISQY  FISQYY
----            -----         --------    -----  ------   -----  ------
CHARNEY         ROSS          1998/09/12  98 Q4  1998 Q4  Q4 98  Q4 1998
CHIEN           CHRISTINE     1997/10/01  98 Q1  1998 Q1  Q1 98  Q1 1998
CLEVELAND       PHILIP        1996/07/30  96 Q4  1996 Q4  Q4 96  Q4 1996
CLINE           STEPHEN       1998/11/12  99 Q1  1999 Q1  Q1 99  Q1 1999
COHEN           DANIEL        1997/10/05  98 Q1  1998 Q1  Q1 98  Q1 1998
CORRIVEAU       RAYMOND       1997/12/05  98 Q1  1998 Q1  Q1 98  Q1 1998
COSSMAN         MARK          1996/12/19  97 Q1  1997 Q1  Q1 97  Q1 1997
CRONIN          CHRIS         1996/12/03  97 Q1  1997 Q1  Q1 97  Q1 1997
CROWDER         WESLEY        1996/09/17  96 Q4  1996 Q4  Q4 96  Q4 1996
CULLEN          DENNIS        1995/09/05  95 Q4  1995 Q4  Q4 95  Q4 1995
CUMMINGS        JAMES         1993/07/11  93 Q4  1993 Q4  Q4 93  Q4 1993
CUTLIP          GREGG         1997/03/26  97 Q2  1997 Q2  Q2 97  Q2 1997