Excel Operators

When creating a dynamic retrieval using DLXRanger, three dynamic end date options are available – last period, this period, and end of data series. Each of these three options has a corresponding operator that is written to the date range to indicate the selection – an asterisk/star (*), a pound/hash (#) and an exclamation mark (!), respectively. In addition, a tilde (~) must be used to denote end of data series if a rolling range is requested. The difference between ! and ~ is described below.

 

Last period (*)

The DLX software will bring in data through the prior period.

For example, if monthly data are being retrieved in April 2013, DLX will bring in data for all series in the retrieval through March 2013. This operator can be used in rolling ranges.

 

This period (#)

DLX will bring in data through the current period. For example, if monthly data are being retrieved in April 2013, DLX will bring in data for all series in the retrieval through April 2013 (this month). This operator can be used in rolling ranges.

 

End of data series (!)

DLX will check each series in your retrieval for its end date and bring in data through that end date for each individual series. For example, if you had two monthly forecasts, one ending in December 2016 and the other ending in March 2017, DLX will bring in data for the first series through December 2016 and the second through March 2017. This operator should be used to retrieve data when you want cells that follow to remain unchanged. For example, forecast formulas will not be overwritten with NAs. The end of data series operator does not work with rolling ranges [see next entry, end of data series (~)] .

 

End of data series (~)

The tilde (~) retrieval works much the same as the exclamation mark (!), retrieving data for each individual series in your retrieval through the end date of that series. A unique feature of the tilde (~) operator is that it will clear 5 cells of information below each column of your retrieval when displaying data in columns (or 5 cells of information to the right of your retrieval if displaying data in rows). Also, unlike the exclamation mark (!), this operator can be used in conjunction with rolling ranges. Please note that the end date that is used for a rolling range is determined by the first series in your retrieval.