Here are lists of the most used DR Functions and DR Named Ranges.
- DR functions start with DR. (period)
- DR named ranges start with DR_ (underscore)
Primary DR Functions
| DR Name | Description | Example | Output Type |
|---|---|---|---|
| DR.GET | Returns the result of a given DR function. It shows a single data point without aggregations. | "How much did we spend in marketing expenses last month?" | Varies |
| DR.MTD | Returns the aggregated results of a given DR function from the first day of the month until a given date. | "How much revenue did we have from the beginning of the month until today?" | Number |
| DR.MTG | Returns the aggregated results of a given DR function from the a given date until the end of the month. | "What's our SG&A expense forecast for the remainder of the month?" | Number |
| DR.QTD | Returns the aggregated results of a given DR function from the first day of the quarter until a given date. | "What's the salary expense from January 1st to today, February 24th?" | Number |
| DR.QTG | Returns the aggregated results of a given DR function from the a given date until the end of the quarter. | "How much travel expense did we incur between last year today and the end of that quarter?" | Number |
| DR.ROLLINGMONTHS | Returns the aggregated results of a given DR function from the range of dates from the date until given dates. | "How much revenue did we forecast between September of last year and today?" | Varies |
| DR.YTD | A value for year to date | "What are our sales year-to-date?" | Varies |
| DR.YTG | A value for the remainder of the year | "What's our sales forecast from today until the end of the year?" | Varies |
Internal DR Functions
| DR Name | Description | Example |
|---|---|---|
| DR.DATE | Convert date value to an excel value formatted as date. | DR.date("1/31/2022") = 44592 |
| DR.FORECAST | Returns the second argument if the first argument is "Forecast", return "Null" otherwise. | DR.forecast("Actuals","10+2 (2022)") = "Null", "Actuals","10+2 (2022)") = "10+2 (2022)" |
| DR.INCLUDE* | Should be a part of DR.GET/DR.YTD/DR.YTG/… function. Will help you to include few arguments for one field. | DR.GET([Month],DR.Include("Jan","Feb"),[...]) shows data as a sum of January and February values. |
| DR.RANGE** | Should be a part of DR.GET/DR.YTD/DR.YTG/… function. Will help you to include range of dates as a parameter. | DR.GET([Month],DR.Range("Jan","Mar"),[...]) shows data as a sum of January, February, and March values. |
| DR.MONTH | Should be a part of DR.GET/DR.YTD/DR.YTG/… function. Will help you to include a full month instead of using date ranges. | DR.GET([Reporting Month], DR.MONTH(31/01/2024)) shows data as a sum of January values. |
| DR.QUARTER | Should be a part of DR.GET/DR.YTD/DR.YTG/… function. Will help you to include a full quarter instead of using date ranges. |
DR.GET([Reporting Month], DR.QUARTER(31/01/2024)) shows data as a sum of January, February and March 2024 values. Fiscal Year starts at January. |
| DR.YEAR | Should be a part of DR.GET/DR.YTD/DR.YTG/… function. Will help you to include a full year instead of using date ranges. |
DR.GET([Reporting Month], DR.YEAR(31/01/2024)) shows data as a sum of January-December 2024 values. Fiscal Year starts at January. |
The DR.INCLUDE function is not supported in published items. Additionally, DR.RANGE and DR.INCLUDE do not work together.
DR Named Ranges
| DR Name | Description | Input Type | Output Type |
|---|---|---|---|
| DR_COLLECTION_PROCESS | Returns the name of the collection process if the file is connected to collection process | Text | Text |
| DR_FILEBOX_NAME | The name of the FileBox | Text | Text |
| DR_LAST_REFRESH_ALL | Last time the file was refreshed | Date-time | Date-time |
| DR_TAG_DATE | The date of the Filebox | Date | Date |
| DR_TAG_DATE_MONTH | The month of the date tag based on the date tag | Number | Number |
| DR_TAG_DATE_SOMOTNH | The first day of the month, based on the file date tag | Date | Date |
| DR_TAG_DATE_YEAR | The year of the date tag, based on the file date tag | Number | Number |
| DR_[DYNAMIC_RANGE_ ID]FIELD[I] DR_36_FIELD_1 |
Relevant for dynamic ranges only. Will return the name of the display field name, based on the dynamic range id and the display field number | Text | Text |
| DR_[DYNAMIC_RANGE_ ID]_FIELD_[I] |
Returns the name of the field based on the dynamic range name and the field name | Text | Text |
Note that you can use all existing Excel formulas in combination with Datarails formulas. For example, you may have a DR.GET formula in a column for each month to display respective expenses. If you'd like to show the average expense amount, you can use Excel's AVERAGE() formula referencing the columns with Datarails formulas to get the desired result.
© Datarails Ltd. All rights reserved.
Updated
Comments
0 comments
Article is closed for comments.