Use the SELECT function to return values from a given list item or time period.
For example, you can use the SELECT function to compare values between different versions.
Note: We don't recommend the use of the SELECT function in conjunction with non-generic time periods. Use of SELECT goes against the sustainable nature of model building, where hard-coded elements can cause issues when updating the timescales of the model.
Use SUM or LOOKUP on modules with Time-formatted items, instead.
Syntax
Source[SELECT: Target item]
Arguments
| Argument | Data type | Description |
| Source | Number, Boolean, date, time period, list, or text | The source module and line item to select a value from. This must be a module line item module.lineitem. |
| Target item | Time period, list | The item for the SELECT function to return the value from. This should be a List name.'list item' or Time.'time period'. |
The SELECT function returns a value of the same data type as the Source argument.
Calculation engine functionality differences
In Polaris, the reference in SELECT must be literal, rather than an expression, except you can use a literal offset from the current time period, Time.'Feb 23' + 1.
In the Classic Engine, SELECT can use expressions. The Classic Engine allows Time.'Current Period' in the expression for SELECT, but you can't use any other named Time period in expressions.
Syntax example
Income Statement.Sales[SELECT: Versions.Actual]
Excel equivalent
Examples
List example
In this example, there's a source and a target module.
The structure of the Products list within the Grid View of the General lists is as below:
| Parent | Code | |
| Nuts | ||
| Bolts | ||
| Screws | ||
| Nails |
Source module
The source module, Sales, has the Products list on rows, three line items Gross sales, Cost of goods, and Gross profit on pages, and Time period on columns. The line item Gross sales is selected on pages.
| Jan 22 | Feb 22 | Mar 22 | |
| Nuts | 1,000 | 8,000 | 9,000 |
| Bolts | 2,000 | 7,000 | 10,000 |
| Screws | 3,000 | 6,000 | 11,000 |
| Nails | 4,000 | 5,000 | 12,000 |
Target module
The target module, Sales by products, has the seven line items Nuts, Bolts, Screws, Nails, Total sales, Total cost of goods, and Gross profit on rows, and Time period on columns.
| Jan 22 | Feb 22 | Mar 22 | |
NutsSales.Gross sales[SELECT:Products.Nuts] | 1,000 | 8,000 | 9,000 |
BoltsSales.Gross sales[SELECT:Products.Bolts] | 2,000 | 7,000 | 10,000 |
ScrewsSales.Gross sales[SELECT:Products.Screws] | 3,000 | 6,000 | 11,000 |
NailsSales.Gross sales[SELECT:Products.Nails] | 4,000 | 5,000 | 12,000 |
Total salesNuts + Bolts + Screws + Nails | 10,000 | 26,000 | 42,000 |
Total cost of goodsTotal sales * - 0.6 | -6,000 | -15,600 | -25,200 |
Gross profitTotal sales - Total cost of goods | 16,000 | 41,600 | 67,200 |
Time period example
There are two modules in this example, which use the SELECT function to select values for certain time periods.
Source module
The source module, Product sales, has the Time dimension on columns and the Organization list on rows. Line items and the Products list are on pages, and the Gross sales line item and Nuts list item are selected.
| Oct 21 | Nov 21 | Dec 21 | Jan 22 | Feb 22 | Mar 22 | |
| London | 8,000 | 7,500 | 10,000 | 9,000 | 6,500 | 5,000 |
| Paris | 25,000 | 28,000 | 29,500 | 28,000 | 24,500 | 26,000 |
| Berlin | 5,000 | 7,500 | 10,000 | 6,500 | 2,000 | 6,000 |
Target module
The target module, Recent sales, has the Organization list in columns and line items in rows. The Products list is on pages, and the Nuts list item is selected.
The current period in the Model Calendar is Mar 22, the Year To Date Summary is enabled. The start period of the calendar is Oct 21.
| London | Paris | Berlin | |
Sales over all periods
| 46,000 | 161,000 | 37,000 |
Sales this year to date
| 20,500 | 78,500 | 14,500 |
Sales in the current period
| 5,000 | 26,000 | 6,000 |
Versions example
This example demonstrates how to use SELECT to compare two versions, Forecast and Actual, without having to add an extra version. Adding extra versions increases the size of the model.
There are two modules in this example. The source module, Income statement, and the target module, Income statement variance.
Source module
The source module, Income statement, has the Organization list on rows. The Versions dimension is on columns. Line items and the Time dimension are on pages. The Sales line item and Apr 22 time period display.
You want to pull the data for the Sales line item for the Actual and Forecast versions into the target module.
| Actual | Forecast | |
| London | 975 | 1,000 |
| Birmingham | 1,021 | 1,000 |
| UK | 1,996 | 2,000 |
| Paris | 1,234 | 1,000 |
| France | 1,234 | 1,000 |
| Total company | 3,230 | 3,000 |
Target module
The target module, Income statement variance, has line items on columns, and the Organization list on rows. The Time dimension is on pages and the Apr 22 period displays.
| London | Birmingham | UK | Paris | France | Total Company | |
Actual salesIncome Statement.Sales[SELECT: Versions.Actual] | 975 | 1,021 | 1,996 | 1,234 | 1,234 | 3,230 |
Forecast salesIncome Statement.Sales[SELECT: Versions.Forecast] | 1,000 | 1,000 | 2,000 | 1,000 | 1,000 | 3,000 |
VarianceActual sales - Forecast sales | -25 | 21 | -4 | 234 | 234 | 230 |
You can see that the values in the Actual sales and Forecast sales line items are selected from the Actual and Forecast versions in the source module. The Variance values come from the simple formula Actual sales - Forecast sales.
