RELATIVE DATE
Description
Conditionally returns a value if a date falls within a specified date range. This uses Smart Filtering to select records by date.
Parameters
Options
| Input Parameter Values | Input Values |
|---|---|
| Date | Column or Date Literal (Required) |
| Compare to Date | Column or Date Literal |
| Relative Offset | Numeric Value |
| Offset Measure | Inception to Date, Inception to End of Month, Month, Quarter, Week, Year, Year to End of Month, or YTD |
| Value to Roll Up | Column or Literal |
| Only Rollup If | Generic Criteria |
Remarks
This feature is used by the Organized Date Trend wizard and is an Advanced Calculation.
The Relative Date calculation and Organized Dates Trend are only relevant for calendar year reporting, not non-calendar or 445 fiscal year reporting. The Relative Date calculation is not typically used without using the trend first.
Note: for those users who want to clear out the default smart column value created by a Trend Wizard, ""show"" the selected date column in the grid, e.g. Invoice Date, and clear the filter default <> associated to the QBE filter.
Will control the selection of the report to optimally return just the records required by the calculation.
Example
You want to see the Gross Amount for the past year, depending on a specific date, 06/22/2017. Create a Reference Date parameter, then link it to a Relative Date calculation. The Reference Date parameter should look like the image below.
Once you have created the parameter, enter the date you want to see the Gross Amount for, in this case 06/22/2017. Go into the calculation editor and create a Relative Date calculation link the "Compare to Date" to the Reference Date parameter/ variable, along with inputting the other required values. If you want to bring back the Gross Amount for the past year from the date you input into the Reference Date parameter, according to the Due Date, your values should be as follows in the Calculation Editor.
You should now be seeing the QBE of the Due date filtered on the past calendar year (Between 01/01/2016 and 12/31/2016) for the date you've input into the Reference Date Parameter(06/22/2017).
