The following functions compliments Consolidated XL functions used to hot link Period Heading Names. GLYearPeriodName() and GLPeriodDate() are used to get Dates relating to the given parameters.

GetRelativePeriod() and GetRelativeYear() are used to drive our wizard reports. Given a target Year/Period they can calculate the correct Year/Period to get according to the current required period structure (Accounting/Calander Periods/Quarter and Weeks). Allowing the designer to add column headings which when the user changes Period/Year parameters return Period/Year headings for display, which would be tricky to do with your own fomrula.

   The Wizard "Get GL Values Example Wizard" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.

Get Period Name

Below is an example of output for Accounting Period, Accounting Quarters, Calendar Month (Jan-Dec), or 52 weeks of the Year (Jan-Dec).

   For a tutorial on this function, click here

Parameters:

Parameter	Description
CompCode	Short code assigned by Consolidated XL, or by you to identify your company. You can change this code in the Consolidated XL Company settings
Year	0 gives current year, -1 Previous … 2023 gives specific Year
Period	0 Current Period 1-12 gives specific Turnover for Period
[Style]	"Standard" gives Y/P, or Y/Qtr "Long" gives Y/MM, or Y/Qtr YY gives 4 digit year Y gives 2 digit year P gives Period Number M gives Calendar Month Number MM gives Short Month Name MMM gives Full Month Name W gives "Wkx" if weeks K gives "x" if weeks
[Options]	Optional “” or beginning with “A” gives by Financial Accounting Period beginning with “AQ” Financial Accounting Period Quarter beginning with “CQ” Calendar Period Quarter beginning with “C” gives by Calendar Month beginning with “W” gives by Calendar Week (Week 1 starting first Monday in year)

Examples:

Example Query	Function and Parameters	Value Returned
Get "Year/Period" Number for Current Period	=CXL.GetPeriodName( “EXAMP01”,0,0,"","YY/P" )	2023/6
Get "Period" Number for Current Period	=CXL.GetPeriodName( “EXAMP01”,0,0,"","P" )	6
Get "Short Year/Period" Number for Previous Period	=CXL.GetPeriodName( “EXAMP01”,0,-1,"","Y/P" )	23/5
Get "Year/Period Short Month" for Previous Period Month	=CXL.GetPeriodName( “EXAMP01”,0,-1,"","YY/M" )	2023/06
Get "Period Short Month" for Previous Period Month	=CXL.GetPeriodName( “EXAMP01”,0,-1,"","M" )	06
Get "Period Month" for Previous Period Month	=CXL.GetPeriodName( “EXAMP01”,0,-1,"","MM YY" )	Sep 2023
Get "Period Month" for Previous Period Month	=CXL.GetPeriodName( “EXAMP01”,0,-1,"","MMM YY" )	September 2023
Get "Week No" for Previous Period Month	=CXL.GetPeriodName( “EXAMP01”,0,-1,"W","P" )	23

Get Period Start Date

Function GetPeriodDate returns the date of the first day of a Period. Excel formatting can be then used to display in your desired format.

Parameters:

Parameter	Description
CompCode	Short code assigned by Consolidated XL, or by you to identify your company. You can change this code in the Consolidated XL Company settings
Year	0 gives current year, -1 Previous … 2023 gives specific Year
Period	0 Current Period 1-12 gives specific Turnover for Period
[Options]	“” or beginning with “A” gives by Financial Accounting Period beginning with “AQ” Financial Accounting Period Quarter beginning with “CQ” Calendar Period Quarter beginning with “C” gives by Calendar Month beginning with “W” gives by Calendar Week (Week 1 starting first Monday in year)

Examples:

Example Query	Function and Parameters
Get Period Start Date for Current Period	=CXL.GetPeriodDate( “EXAMP01”,0,0,"" )
Get Period Start Date for Previous Period	=CXL.GetPeriodDate( “EXAMP01”,0,-1,"" )
Get Period Start Date for Year 2023 Period 1	=CXL.GetPeriodDate( “EXAMP01”,2023,1,"" )
Get Period Start Date for Year 2023 Period 2	=CXL.GetPeriodDate( “EXAMP01”,2023,2,"" )

Relative Period functions

Function GetRelativePeriod() and GetRelativeYear() return Period or Year relative to the input Period or Year. This allows a cell to contain Year and Period, say period 3, 2023, and in a series of cells go back 6 periods but adjust the Year and Period accordingly by X periods in a series of columns.

   For a tutorial on this function, click here

Parameters:

Parameter	Description
InputYear	0 gives current year, -1 Previous … 2023 gives specific Year
InputPeriod	0 Current Period 1-12 gives specific Turnover for Period
X	The number of Periods to adjust by -2 takes 2 periods from the InputYear and InputPeriod.
[Options]	“” or beginning with “A” gives by Financial Accounting Period beginning with “AQ” Financial Accounting Period Quarter beginning with “CQ” Calendar Period Quarter beginning with “C” gives by Calendar Month beginning with “W” gives by Calendar Week (Week 1 starting first Monday in year)

Examples:

In a series of columns we have period 3, 2023 in the last column on the right. We need asdjust back 6 periods.

Example Query	Function and Parameters
Last Column on the right period 3	=CXL.GetRelativePeriod( 3,0,"C" )
Last Column on the right 2023	=CXL.GetRelativeYear( 3,0,"C" )
First colunn (-6 Preiods) gives period 7	=CXL.GetRelativePeriod( 2023,-6,"C" )
First colunn (-6 Preiods) gives 2022	=CXL.GetRelativeYear( 2023,-6,"C" )

Example of this in Action

In CXL Wizards, where the user enters a Year and Period and displays multiple periods in columns, functions are used to drive the Period and Year for the column below. As you can see in the screenshot below:

"P&L / Income Statement" and "Sales report" wizards populate Row 4 with -11 to 0, and CXL.GetRelativeYear() and CXL.GetRelativePeriod() calculate a Period and Year based on the current options. Thus, if the Period Aging is changed to Quarters, or Weeks, the Periods and Years are calculated accordingly.

Column G above calculates Relative Period -8 (back 8 periods in G4), creating a column period of 11/2022 from the last period of 7/2023 (set in C16 and C17).

   Reference Guide CXL.GetRelativeYear( CompCode, InputYear, X, [Options] ) and CXL.GetRelativePeriod( CompCode, InputPeriod, X, [Options] )

   The Income Statement/P&L Wizard uses these functions as shown in the screen shot.