AX1288

Using years as variables in file groups

If a file group year is defined for the file group, you can use that value as a variable in the file group name and in other file group variables. You can also use built-in variables that automatically calculate a number of years before and after the file group year. Using this approach, you can dynamically update all associated years for a file group by changing one value.

File group year as a variable

To use the defined file group year as a variable, use the following syntax:

The FileGroupYear variable returns the full year value (for example: 2020), and the ShortFileGroupYear variable returns the two-digit version of the year (for example: 20). The two-digit version is automatically calculated based on the value for the file group year.

Either of these variables can be used in the file group name, so that the current file group year is always reflected in the name (for example, "Budget 2020"). You can type the variable into the File Group Name field directly, or you can click the use in file group name link that displays to the right of the box after the file group year is defined. (If you want to use the ShortFileGroupYear in this field then you must manually type the variable.)

If the variable is present in the file group name, the resolved name will display in gray text to the right of the File Group Year box, as shown in the following screenshot:

The year variables can also be used in the file group display name and the tab prefix, as well as in other file group variables.

Calculate years based on the file group year

File groups often query and save data from one or more years of data tables. For example if the purpose of the file group is to calculate budget data for the 2020 budget, then the file group may query data from GL2018 and GL2019, and it may save data to BGT2020. Therefore it is often useful to automatically calculate years before and after the file group year, for use in file group variables and table variables.

Axiom Software provides four built-in variables to perform these calculations:

• FileGroupYearPlusX
• FileGroupYearMinusX
• ShortFileGroupYearPlusX
• ShortFileGroupYearMinusX

When using these variables, change the X to the number of years to calculate before or after the file group year. For example, if the file group year is 2020 and you want to return the previous year, you would use FileGroupYearMinus1.

You might create a set of year-related variables for use in plan file column headers, and then also reference those year variables in your table variables. For example, if the file group year is 2020 you might have some variables and table variables as follows:

This approach is useful when changing the file group year as part of a rollover process. When the file group year is changed from 2020 to 2021, then the results of the year variables and table variables will update accordingly.

Summary of built-in year variables

The following year-related variables are available for all file groups. These built-in variables can be used within other user-defined variables, and/or the values for these variables can be returned directly using the GetFileGroupVariable function.

Variable	Description
{FileGroupYear}	The year for the file group as defined in the file group properties. This must be a four-digit year entry such as 2020. All other year-related variables are dependent on a valid entry for the file group year.
{FileGroupYearMinusX}	Automatically calculates and returns years prior to the file group year, given a value for X. For example, enter 1 for X if you want to return the value of the previous year (file group year minus 1). If FileGroupYear is 2020, then FileGroupYearMinus1 is 2019.
{FileGroupYearPlusX}	Automatically calculates and returns years after the file group year, given a value for X. For example, enter 1 for X if you want to return the value of the next year (file group year plus 1). If FileGroupYear is 2020, then FileGroupYearPlus1 is 2021.
{ShortFileGroupYear}	Automatically calculates and returns the short version of the file group year. For example, if FileGroupYear is 2020, then ShortFileGroupYear is 20.
{ShortFileGroupYearMinusX}	Automatically calculates and returns the short version of years prior to the file group year, given a value for X. For example, enter 1 for X if you want to return the value of the previous year (short file group year minus 1). If ShortFileGroupYear is 20, then ShortFileGroupYearMinus1 is 19.
{ShortFileGroupYearPlusX}	Automatically calculates and returns the short version of years after the file group year, given a value for X. For example, enter 1 for X if you want to return the value of the next year (short file group year plus 1). If ShortFileGroupYear is 20, then ShortFileGroupYearPlus1 is 21.