1. Calculation functions
  2. All functions
  3. Logical functions
  4. SELECT

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.

Syntax

Source[SELECT: Target item]

Arguments

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.

Syntax example

Income Statement.Sales[SELECT: Versions.Actual]

Excel equivalent

LOOKUP(opens external page)

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 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

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 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

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. 

 ActualForecast
London9751,000
Birmingham1,0211,000
UK1,9962,000
Paris1,2341,000
France1,2341,000
Total Company3,2303,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.


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.

Disclaimer

We may update our documentation occasionally, but will only do so in a way that does not negatively affect the features and functionality of the Anaplan service.