Search

SELECT

The SELECT function is used to identify a list item to use from one or more hierarchy lists to filter the source module data. This function works in conjunction with the other dimensions in the module to return dependent values.

Syntax

x[SELECT: y]

where:

  • x: the value(s) to use as reference
  • y: the location of the source data

Format

Input Format Output Format

x: Number, Boolean, date, time period, list, text

y: Time period, list

Matches the data format used by argument x

Arguments

The function uses the following arguments:

  • x: Number, Boolean, date, time period, list, text
  • y: Time period, list

Constraints

The function has no constraints.

There is more information available on the Time Aggregation page.

Excel equivalent

Back to Top

Example

Sales Module

  • Pages
    • Line items – Gross Sales, Product List
  • Columns – Time (Model timescale = Months)
  • Rows - Organization

Sales Breakdown Module

  • Pages – Organization, Product List
  • Columns – Time
  • Rows – Line items – Products from Product List
    • Blueprint Applies To - Product List, Organization

SELECT Function in action

Sales Module.Gross Sales[SELECT:Product List.Nuts]        

The function reads the Gross Sales numbers from the Sales Module for each product based on the Organization and Date settings in the page and column.

LISTITEM Hierarchy

Sales Module

  • Pages
    • Line items – Gross Sales, Product List
  • Columns – Time (Model timescale = Months)
  • Rows - Organization

Sales Breakdown Module

  • Pages – Organization
  • Columns – Time
  • Rows – Line items – Product

Products list rolls up into a Product List so;

  • Hardware is parent to Nuts, Bolts, Screws, and Nails;
  • Paint is parent to Interior, Exterior, Metal, and Specialist;

SELECT Function in action

Sales = Sales Module.Gross Sales[SELECT: Product.Hardware]        

The function totals the Gross Sales numbers from the Sales Module of all child products of Product hardware based on the Organization and Date settings in the page and column.

Time Period

You can apply a SELECT formula to different time periods. In Model Settings > Time, select the Calendar Type as Weeks. Mark the Quarter Totals, Half Year Totals, Year To Date, Year To Go, and Total of All Periods check boxes.

SELECT Function in action

Absolute Time reference

This means we can use SELECT on the following time periods by adding the expression to the line items:

SELECT: Time. All Periods
SELECT: Time. 'FY14'
SELECT: Time. YTD
SELECT: Time. YTG
SELECT: Time. 'H1 FY14'
SELECT: Time. 'Q1 FY14'
SELECT: Time. 'Jan 14'
SELECT: Time. 'Week 21 FY14'
Relative Time Reference

Suppose you want to create a module for current period values, where the values will change automatically when the current period changes and this saves you having to update the current period manually. You can use SELECT with the Time.Current Period parameter.

SELECT using Time.Current Period

Suppose you want to create a module for current period values, where the values will change automatically when the current period changes and this saves you having to update the current period manually. You can use SELECT with the Time.Current Period parameter.

The example model has the current period set at April 2011, and contains a Sales source module showing Gross Sales data by country and city:

A results module for time-aggregated values and current period values uses SELECT with Time.Current Period to get the current period revenue data by country and city:

Back to Top

Similar functions