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: Anaplan does not 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 should follow the syntax below:
|
| Target item | Time period, list | The item for the SELECT function to return the value from. This should follow one of the syntaxes below:
|
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 cannot 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 General lists is as below:
| Total Products | ||
| Hardware | ||
| Nuts | ||
| Bolts | ||
| Screws | ||
| Nails | ||
| Garden | ||
| Tools | ||
| Plants | ||
| Lumber | ||
| Buildings |
The Sales module source module below has the Product list and line items on pages. The Gross sales line item and Nuts product display. The Time and Organization lists are on columns and rows respectively.
| Jan 22 | Feb 22 | Mar 22 | |
| London | 7,000 | 8,000 | 9,000 |
| Paris | 10,000 | 11,000 | 12,000 |
| New York | 12,000 | 14,000 | 15,000 |
| Munich | 15,000 | 16,000 | 17,000 |
The target module has the Organization list on pages and London displays. The Time list is on columns, and line items on rows. Each line item selects the value for each hardware item of the Product list, including the aggregated total. You can see the effect of the SELECT function in the Nuts line item, which selects data from the example source view above.
| Jan 22 | Feb 22 | Mar 22 | |
Nuts
| 7,000 | 8,000 | 9,000 |
Bolts
| 3,000 | 2,000 | 5,000 |
Screws
| 15,000 | 18,000 | 16,000 |
Nails
| 1,000 | 1,500 | 1,250 |
Hardware
| 26,000 | 29,500 | 31,250 |
Time period example
There are two modules in this example, which uses the SELECT function to select values for certain time periods.
The source module, Product sales, has the Time dimension on columns and the Organization list on rows. Line items and the Product list are on pages, and the Gross sales line item and Hardware list item display.
| 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 |
The target module, Recent sales, has the Organization list on columns and line items on rows. The Product list is on pages and the Hardware list item displays.
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 module 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 sales
| 975 | 1,021 | 1,996 | 1,234 | 1,234 | 3,230 |
Forecast sales
| 1,000 | 1,000 | 2,000 | 1,000 | 1,000 | 3,000 |
Variance
| -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.
