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.

Source[SELECT: Target item]

ArgumentData typeDescription
SourceNumber, Boolean, date, time period, list, or text

The source module and line item to select a value from.

This should follow the syntax below:

module.lineitem 

Target itemTime period, list

The item for the SELECT function to return the value from.

This should follow one of the syntaxes below:

List name.'list item'

Time.'time period'

The SELECT function returns a value of the same data type as the Source argument.

Income Statement.Sales[SELECT: Versions.Actual]

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.

LOOKUP

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 22Feb 22Mar 22
London7,0008,0009,000
Paris10,00011,00012,000
New York12,00014,00015,000
Munich15,00016,00017,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 22Feb 22Mar 22

Nuts

Sales module.Gross sales[SELECT:Product.Nuts]

7,0008,0009,000

Bolts

Sales module.Gross sales[SELECT:Product.Bolts]

3,0002,0005,000

Screws

Sales module.Gross sales[SELECT:Product.Screws]

15,00018,00016,000

Nails

Sales module.Gross sales[SELECT:Product.Nails]

1,0001,5001,250

Hardware

Sales module.Gross sales[SELECT:Product.Hardware]

26,00029,50031,250

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 21Nov 21Dec 21Jan 22Feb 22Mar 22
London8,0007,50010,0009,0006,5005,000
Paris25,00028,00029,50028,00024,50026,000
Berlin5,0007,50010,0006,5002,0006,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.


LondonParisBerlin

Sales over all periods

Product sales.Gross sales[SELECT: TIME.'Current Period']

46,000161,00037,000

Sales this year to date

Product sales.Gross sales[SELECT: TIME.'YTD']

20,50078,50014,500

Sales in the current period

Product sales.Gross sales[SELECT: TIME.'Current Period']

5,00026,0006,000

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

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. 

 ActualForecast
London9751,000
Birmingham1,0211,000
UK1,9962,000
Paris1,2341,000
France1,2341,000
Total Company3,2303,000

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.


LondonBirminghamUKParisFranceTotal Company

Actual sales

Income Statement.Sales[SELECT: Versions.Actual]

9751,0211,9961,2341,2343,230

Forecast sales

Income Statement.Sales[SELECT: Versions.Forecast]

1,0001,0002,0001,0001,0003,000

Variance

Actual sales - Forecast sales

-2521-4234234230

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.