Relative Period

Description

Take the calculation's parameter criteria and return balances from a balance table, most often the F0902 GL Account Balances table.

Calculation Editor

Options

Input Parameter Values Input Values
Relative Offset Literal Integer
Organize By Inception to Date, Job to Date, Period, QTD, Quarter, Year, Year + Adjustment Periods, or YTD Fiscal Period
Company Column or Literal
Period Literal
Fiscal Year Literal
Date Column or Literal
DateItem Prefix (advanced) Column or Literal
Reverse Sign Criteria Field and Criteria inputs
Add Begin Balance Criteria Field and Criteria inputs
Add Original Budget Criteria Field and Criteria inputs
Accounting Interval 12-Period or 52-Period
Only Rollup If Generic Criteria

Remarks

  • The relative period calculation will either take Period and Fiscal Year inputs or a Date input, but it does not need both options.
  • The 'Add Begin Balance Criteria' setting is required to be set to properly evaluate an 'Inception to Date' total. Without it, The 'Organize By' settings 'Inception to Date' and 'YTD Fiscal Periods' options will return the same totals.

DateItem Prefix

The DateItem Prefix option is utilized when there are multiple period balance columns available in the JD Edwards table being used. Specify the Alias prefix (two-digit code) to tell the calculation which period balance columns to utilize.

For example, in the F06136, there are four different period bin segments. By right-clicking on one of the column captions and going down to the Column Caption section, a user can see that the segments of columns start with aliases of BW, BX, BE, BY. One of these two-digit alias codes would be what goes into the DateItem Prefix section of the calculation input to determine which period columns are being looked at.

In the case of the F0902 or the F1202 which are the two most common balance tables, there is only one set of period columns and therefore the DateItem Prefix section can be left blank.

General Ledger Guidelines used in Quick Reports

For the "Reverse Sign Criteria" and "Add Begin Balance Criteria", an Account Category calculation is created as a part of the Quick Report wizard. This calculation can then adjust necessary object account ranges based on the General Ledger Guidelines that have been set up in JD Edwards' Automatic Accounting Instructions.

If the GLGs have not been properly set up in JD Edwards, a user can adjust the field and criteria being used to determine which records will either reverse the signs or contain beginning balances.

Examples

How to utilize Relative Periods in Quick Reports (Financials)

Relative Period calculations are automatically created when you utilize Quick Reports to create financial statements, fixed asset reports, or job cost reports. This means that while there are still methods of manipulating the relative period calculations in the editor, this example will not include the physical creation of the relative period calculation.

Let's say you've created an income statement inside of Quick Reports that returns the current Fiscal Period based on the parameter inputs you select. You would now like to turn your report into a rolling 12-period income statement. You can do this utilizing the settings of a relative period calculation.

  1. Start by selecting the existing relative period calculated column and copying it 11 times for a total of 12 relative period calculated columns.
  2. Go into Design > Calculations to view all relative period calculations at once.
  3. Start going down the list and adjust the "Relative Offset" of each calculation 1 forward. Example: if your first relative period calculation has a relative offset of 0, your second one will have a relative offset of 1, third will be 2, fourth will be 3 and so on up to 11.

  4. Return to your report and enter in Fiscal Period of 1 and Fiscal Year of your choosing to see an Income Statement showing Periods 1 - 12.

    Note: Some users choose to set up their rolling 12-period income statement in the negative instead. This means that whatever Fiscal Period is put into the parameter, the 12 periods that show up are going backwards from the selection. To do the report that way, the Relative Offsets in the calculated columns will be 0 through -11 rather than 0 through 11.

If you wanted to add in a Fiscal Year column at the end that includes the entire current fiscal year based on the parameter inputs of your report viewer, copy a relative period calculation from what already exists and adjust the "Organize By" option to be "Year". The name will dynamically adjust as well, but feel free to right-click to select "Rename" as well if an adjustment is needed beyond the default.

How to utilize Relative Periods in HR Reports (F06136 - Tax History)

Let's say you want to create a report in your Tax History table that allows users to analyze employees for specific time frames (Current Fiscal Period and Year to Date for example) and review their Gross Pay, you can utilize relative period calculations to accomplish this.

  1. In the F060136 table, start by selecting the columns that you want to be always visible in your report. This will not include any gross pay period bin columns. I have included Address Number (and its associated description of Employee name), Employee Tax ID, Work Tax Area, and Statutory Code in my example.
  2. Because you would like these period and YTD relative period bins to adjust the returned column outputs based on user selection, create a parameter for Fiscal Period set to integer editor type and another for Fiscal Year set to integer editor type.
  3. Select Back on the Parameter Editor and go to Design > Calculations
  4. Create a Relative Period Calculation with the following inputs:
    1. Relative Offset: 0
    2. Organize By: Period
    3. Company: Co (Data Column)
    4. Period: <Fiscal Period> (This is your parameter)
    5. Fiscal Year: <Fiscal Year> (This is your parameter)
    6. DateItem Prefix: Because we are looking to analyze Gross Pay rather than one of the option options available in this table, we will use the code "BW" which is visible when you go to Design > Columns and review the alias names for the Gross Pay columns.
  5. Repeat the above step again, but in Organize By, select YTD Fiscal Period which summarizes the values of all periods from the period 1 to your Fiscal Period parameter selection for the given Fiscal Year.

Now, as you adjust your report parameters, you can watch as your two relative period calculations adjust as well.

Advanced Note: A designer could create a parameter for DateItem Prefix that links to the relative period calculations to allow the end user to adjust the period bin columns they are viewing without having to enter the calculation editor.

Note: The F41112 Item As Of table is another commonly used table for the Relative Period calculation outside of Quick Reports due to its period bins.