Implementing Extended Syntax Operators

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Implement extended syntax operators

Extended Syntax Operators

You specify input and output contexts explicitly with context operators of the extended syntax. The table lists the context operators.

The ForAll and ForEach operators are useful when you have a default context with many dimensions. It is often easier to add or subtract from the context using ForAll and ForEach than it is to specify the list explicitly using the In operator.

Context Operators In and Where

The In operator specifies dimensions explicitly in a context.

The example in the figure, In Context Operator to Specify Dimensions, shows a report with Year and Sales revenue. Your data provider also contains the Quarter object, but you do not include this dimension in the block. Instead, you include an additional column to show the maximum revenue by quarter in each year.

The Where Operator

The Where operator restricts the data used to calculate a measure.

Other examples of using Where:

The variable High Revenue has the formula=[Revenue] Where ([Revenue] > 5000000). When placed in a block, High Revenue displays either the revenue when its value is greater than 500000, or nothing. When placed in a footer at the bottom of the High Revenue column, the formula =Average([High Revenue]) returns the average of all the revenues greater than 500000.

Note
The Where operator is not in the list of context operators that define the dimensions related to a calculation. However, as you can combine the In and Where operators in a formula, the Where operator is documented in this lesson as well.

=Max([Sales revenue In ([Country] ; [Year]) Where ([Country] = "France"))

The In operator specifies the dimensions that feed into the calculation, and the Where operator restricts the data to calculate the measure.

=[Sales revenue] Where ([State] = "California" And [Year] = "2019")

Implement Context Operators In and Where

Context Operator ForEach

See the following video to learn more about the Input Context ForEach.

Implement Context Operator ForEach

Context Operator ForAll

See the following video to learn more about Context Operator ForAll.

Implement Context Operator ForAll

Save progress to your learning plan by logging in or creating an account

Login or Register