Datarails Flex Add-In: Date Ranges in Formulas

Formulas in the Datarails Flex Add-In are powerful tools that allow you to extract and manipulate data efficiently. They consist of three main components: functions, fields, and values. Functions define the calculations to be performed, fields specify the data source, and values provide the specific criteria or filters for the data.

Date ranges can be included as part of the value, enabling you to filter and extract data for specific periods or ranges, enhancing flexibility and precision in your data workflows. Below is a comprehensive guide to using date ranges in formulas.

Overview of DR.RANGE

The DR.RANGE function enables you to define ranges for dates, whole numbers, or decimal numbers when used as part of a DR.GET Formula. However, it cannot be applied to text or Boolean (true/false) data types.

Basic Syntax

DR.RANGE (Value1, Value2)

Examples of DR.RANGE

Required Range Syntax
Dates between 01/01/2024 and 08/31/2024 DR.RANGE(DR.DATE("01/01/2024", DR.DATE("12/31/2024"))
Accounts between 4000 and 5000 DR.RANGE(4000,5000)
Excel Cell Ranges A2-A5 DR.RANGE(A2,A5)

Note: When using hard-coded dates, the DR.DATE function must be included. If using Excel cell ranges, DR.DATE is not required.

DR.RANGE in DR.GET

The syntax to retrieve the value of a field using a date range is as follows:

To fetch the value of the field Amount for dates between 1 January 2024 and 31 December 2024:

DR.GET(Amount, "[Reporting Month]", DR.RANGE(DR.DATE("01/01/2024"), DR.DATE("12/31/2024")))

DR.RANGE in Field Selector

You can also create a DR.RANGE expression using the Field Selector in the Datarails Flex Add-In. Follow these steps: 

  1. Click the cell where you want the function to appear.
  2. In the Excel formula bar, right-click. Select Datarails and Parameters.
  3. The Field Selector dialog box will open. Complete the fields as follows:
    • Select a field with a compatible data type (date, whole number, or decimal). For example, select 'Reporting Month'.
    • Check the option for 'Values in Range (DR.RANGE)'.
    • Set the start date and end date by entering values.
    • Enable the option to Add as the last parameter.
  4. Click 'ok'. The Field and Date range will be added to the Formula bar.

Reference Date (Date Picker) in DR Formulas

Besides referencing values to specific cells, you can also use the Date Picker, which acts as the reference date.

The Date Picker is a tool that sets the reference date for your reports, making them dynamic by allowing you to adjust the reporting period directly within your Excel workbook. The Date Picker serves as your guide for selecting and changing the report's date, enabling you to work through historical periods or the most recent data available. It allows you to choose from various time dimensions, including:

  • Day
  • Week
  • Month
  • Quarter
  • Year

By selecting a time dimension and referencing it in your formula, changing the Date Picker will dynamically update the formula's output.

Example

To bring data for the last month by changing the Date Picker, use the following formula:

DR.GET(Amount, "[Reporting Month]", EOMONTH(DR_DATE_PICKER,-3))


© Datarails Ltd. All rights reserved.

Updated

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.