Functional Selection

Rather than selecting an Element to inject into the formula, users can choose a Functional Selection. This a list of built-in reference functions that allow the user to define a function for selecting the appropriate element without to choosing a specific, hard coded value.

There are two types of functional selection: the functional selection window, and the right click functional selection.

  • Click here to review a list of the functional selections that are available.

Functional Selection Window

The functional selection menu can be opened when defining the elements in a custom formula.

When building formulas, the functional selection menu appears in the Elements panel for the following nodes:

  • Data Point
  • Member
  • Range List

When building custom lists, the functional selection menu appears in the Elements panel for the following nodes:

  • Range List
  • Children (OLAP and Tabular models only)

The given function is driven from the context of the query.

Apply a Functional Selection

Once you've selected a hierarchy, the functional selection menu will appear in the Select Elements panel, next to the search tool. Switch to the functional selection view by clicking the icon. Select the required function. Click the functional selection icon again if you want to switch back to the elements view.

  • Click here to learn how to use the Previous Member function to build custom sets that will enable you to build queries showing the variance between date ranges (i.e., yearly variance, monthly variance, etc).

Right Click Functional Selection

The right click functional selection is available from all Select Elements panels when building custom formulas, custom lists, and dynamic list text parameters. Specifically, it can be accessed from:

  • Data Point (Formula)
  • Member (Formula)
  • Standard List (Formula and List)
  • Range List (Formula and List)
  • Members (Dynamic List Text Parameters)

These functional selections differ from those in the functional selection window, because they are driven either by a given element, or a parameter.

Apply a Right Click Functional Selection

Element

Right click on the required element and click Functional Selection. Choose the relevant function from the dialog.

Parameter

A powerful use of functional selection is to use a parameter to drive the selection in an OLAP or Tabular data model. In this scenario, show business logic from the Elements panel, and right-click on the required parameter. Choose the relevant function from the dialog.

ClosedExample: Right Click Functional Selection on a Parameter
Step 1

Open Model Parameter in Formulate and select an MS OLAP or Tabular model.

Step 2

Create a text parameter. From the Members window, select a Dynamic List. From the Select Hierarchy panel, choose a country regular hierarchy. In the Select Elements panel, open the hierarchy and select each country separately (do not select the top level All element).

Step 3

Once you've completed and saved the text parameter, open the List module in Formulate. Select the same MS OLAP or Tabular model selected in Step 1.

Step 4

Add the Standard List node to the canvas. From the Select Hierarchy panel, open the Customer dimension, and select the Country user hierarchy selected in Step 2. From the Select Elements panel, click the Fx button to show business logic. Right click on the country parameter and select Functional Selections.

Step 5

From the Functional Selections dialog, select Children and click Apply. Save the custom list, and open a Quick Discovery or a new discovery.

Step 6

Add the custom list to Rows, and continue building the query as required. Select a country from the parameter slicer to inject that country's states into the query.

Functional Selections

Some functions are only available when working with MS OLAP, Tabular, SAP BW, SAP Hana data models or Regular SQL hierarchies in Pyramid models.

Function

Definition

Example

Lag

Uses the member that appears “n” positions BEFORE the currently selected element in the given hierarchy, in the context of the query, in the calculation. (Where “n” is the count value entered in the text box”).

If the query has the month “January 2014” selected for dates, the calculation will use July 2013, if lagged by “6”.

Lead

Uses the member that appears “n” positions AFTER the currently selected element in the given hierarchy, in the context of the query, in the calculation. (Where “n” is the count value entered in the text box”).

If the query has the month “January 2014” selected for dates, the calculation will use July 2014, if lead by “6”.

Next Member

Uses the member that appears 1 position AFTER the currently selected element in the given hierarchy, in the context of the query, in the calculation.

If the query has the month “January 2014” selected for dates, the calculation will use February 2014.

Previous Member

Uses the member that appears 1 position BEFORE the currently selected element in the given hierarchy, in the context of the query, in the calculation.

If the query has the month “January 2014” selected for dates, the calculation will use December 2013.

Current Member

Uses the currently selected element in the given hierarchy, in the context of the query, in the calculation.

If the query has the month “January 2014” selected for dates, the calculation will use January 2014.

Parent

Uses the member that is the parent element of the currently selected element in the given hierarchy, in the context of the query, in the calculation.

If the query has the month “January 2014” selected for dates, the calculation will use Quarter 1 2014.

First Child

Uses the first child member of the currently selected element in the given hierarchy, in the context of the query, in the calculation.

If the query has the month “January 2014” selected for dates, the calculation will use Quarter 1 2014.

Last Child

Uses the last child member of the currently selected element in the given hierarchy, in the context of the query, in the calculation.

If the query has the month “January 2014” selected for dates, the calculation will use January 31st 2014.

First Sibling

Uses the first child member that shares the same parent element as the currently selected element in the given hierarchy, in the context of the query, in the calculation.

If the query has the month “February 2014” selected for dates, the calculation will use January 2014.

Last Sibling

Uses the first child member that shares the same parent element as the currently selected element in the given hierarchy, in the context of the query, in the calculation.

If the query has the month “February 2014” selected for dates, the calculation will use March 2014.