Restrict and Lookup

The functionality of the Restrict and Lookup formulas is very similar. They differ in the way the results are displayed, showing a breakdown of aggregated numbers.

Syntax

Syntax:

RESTRICT([<account member>], [<POV>])

LOOKUP([<account member>], [<POV>], [<Ignore Dimension>])

Note

The Restrict formula requires both parameters.
The Lookup formula requires the <account member> parameter, plus one or both of the other parameters.
The Lookup and Restrict formulas cannot be used in combination with other formulas.
When using formulas in SAP BW models, Lookup and Restrict cannot be restricted to dimensions if the measure used is already restricted to this dimension (this is also true for nested constraints).
You can exclude members from the point of view using the != operator.
Both functions support one or more dimension attributes or properties.

Parameter	Usage
<account member>	Specifies an account member in the model.
<Point of View>	This is a list containing selected dimensions and their members: `[d/<dimension name>=<account member1>, <account member2>]` This is used to further restrict data in the account member (see following example 1). Additionally, you can use time navigation syntax to identify specific periods (see example 2 and the following examples, and the dynamic time navigation functions below). If dimension member values are strings, enclose them in quotation marks: `[d/Employee]="e1"` If the dimension has multiple values, group them in parentheses: `[d/Employee]=("e1","e2")` You can use multiple POV parameters and ignored dimensions by using the AND keyword. See example 3 below. You can use dimension attributes or properties in the POV. See example 5 below.
<Ignore Dimension>	A dimension that will be ignored.

Parameter

Usage

Specifies an account member in the model.

This is a list containing selected dimensions and their members:

[d/<dimension name>=<account member1>, <account member2>]

This is used to further restrict data in the account member (see following example 1).

Additionally, you can use time navigation syntax to identify specific periods (see example 2 and the following examples, and the dynamic time navigation functions below).

If dimension member values are strings, enclose them in quotation marks:

[d/Employee]="e1"

If the dimension has multiple values, group them in parentheses:

[d/Employee]=("e1","e2")

You can use multiple POV parameters and ignored dimensions by using the AND keyword. See example 3 below.

You can use dimension attributes or properties in the POV. See example 5 below.

A dimension that will be ignored.

Example:

RESTRICT([Income],[d/Employee]=("e1","e2"))
RESTRICT([Sales],[d/Date]=Previous("Year", 1).Next("Day", 5))
LOOKUP([Sales],[d/Employee]="Anne" and [d/Country]="Spain" and [d/Product]=("Paper","Glue"), [d/Country] and [d/Product])
RESTRICT([SalesRevenue],[d/Products]!="Apple Juice")
RESTRICT([NetRevenue],[d/ResponsibilityCenter].[p/currency]="CAD")

An extensive set of keywords is available for time navigation. The following examples show how these are used:

LOOKUP([Sales],[d/Date]=Next("Year",1).Current("Month"))
RESTRICT([Sales],[d/Date]=Next("Year",1).Last("Quarter",1).Last("Month",1))
LOOKUP([Sales],[d/Date]=Next("Year",1).LastPeriods("Month",2))

Dynamic Time Navigation Functions

These functions can be used only in the POV parameter of the Restrict and Lookup formulas. To access them in the formula bar, press Ctrl + Space after the [d/Date] = string, and select Set Dynamic Time Filter.

Except for the LastPeriods function, these functions can be combined using periods. Ensure that the Granularity order is correct.

Example: Previous("Year",4).First("Quarter",3).Last("Month",6))

Function	Syntax	Parameters	Meaning	Example (based on the date 2017.07.14)
First	`First("Quarter",3)`	(String Granularity, Number N)	The Nth member relative to the parent granularity; for example, the Nth month of the quarter. Not available for “Year” (because there is no parent).	`First("Month",2).First("Day",1)` refers to 2017.08.01. This is the first day of the second month of the current quarter of the current year. Note: if the default hierarchy is Year/Month/Day (that is, there is no Quarter level), then the above example would instead refer to 2017.02.01.
Last	`Last("Month",6)`	(String Granularity, Number N)	The Nth-last member relative to the parent granularity; for example, the Nth-last quarter of the year. Not available for “Year” (because there is no parent).	`Last("Quarter",2).First("Month",3).Current("Day")` refers to 2017.09.14. This is the current day of the third month of the 2nd-last quarter of the current year.
Previous	`Previous("Quarter",2)`	(String Granularity, Number N)	The Nth member before the current one.	`Previous("Month", 1)` refers to 2017.06.14.
Next	`Next("Year",4)`	(String Granularity, Number N)	The Nth member after the current one.	`Next("Year", 3)` refers to 2020.07.14.
Current	`Current("Year")`	(String Granularity)	The current member. This is the default value.	`Current("Day")` refers to 2017.07.14.
LastPeriods	`LastPeriods("Year",4)`	(String Granularity, Number N)	Sums the (aggregated) values of the last N members of the given granularity (including the current one).	`LastPeriods("Year",3)` sums the aggregated values of 2017, 2016, and 2015.

Note

If you use the dynamic time navigation functions, the time dimension needs to be part of the drill state. Consider adding the time dimension to the list of required dimensions so that a story builder adds the time dimension to the drill state.
In stories, use the default date hierarchy when working with accounts that use dynamic time navigation. When a widget doesn’t use the default date hierarchy, these accounts don’t show any data.
When working with planning model data in a story, users can enter data on restricted measures that use dynamic time navigation. For more information, see Entering Values with Dynamic Time Filters.

How to Use Restrict and Lookup

In the case where an aggregated number is based on a dimension where some members in the hierarchy have been excluded by the filter, there will be gaps in the selected data. When you analyze such an aggregated number by drilling down to lower levels, how should these gaps be handled and what should be shown? These formulas provide two solutions:

Restrict – shows no value for rows or columns where no data has been selected.
Lookup – simply shows the aggregated value for all lines (including filtered out members).

Here are a number of examples that show how these two solutions can be implemented, first by comparing Restrict and Lookup, and then by showing how the additional IgnoreDimension parameter for Lookup can be used.

All the following examples are based on a simple dataset showing revenue for three products and two countries:

Product	Country DE	Country FR
Paper	1$	4$
Plastic	2$	8$
Glue	16$	32$

Restrict()

In this example, select two of the three products (paper and glue):

Example

RESTRICT([MyAccount],[d/Product]=("Paper","Glue"))

Total value returned: 53$. That is, an aggregated value for all paper and glue from all countries.

Further analysis: using Restrict, if you drill down further in the product dimension, any lines for products that were not selected will show empty cells:

Lookup()

Lookup can be used with exactly the same parameters for the same purpose. But the behavior for handling lower-level values is different. This example formula makes exactly the same selection as the previous Restrict example:

Example

LOOKUP([MyAccount],[d/Product]=("Paper","Glue"))

Total value returned: 53$. That is, an aggregated value for all paper and glue from all countries.

Further analysis: using Lookup, if you drill down further in the product dimension, Lookup will show a value for all lines:

All selected lines will show the same total value.
The same value is also used for all other products that have not been selected (in this example, Plastic).

Lookup Using the Ignore External Dimension Parameter

The next examples follow on from the previous scenario to show the behavior of Lookup using the additional parameter IgnoreDimension.

Example

In this first example, the same dimension filter for Paper and Glue is applied, and the IgnoreDimension parameter is used to disregard the country dimension:

LOOKUP([MyAccount],[d/Product]=("Paper","Glue"),[d/Country])

Total value returned = 53$. That is, values for all paper and glue from all countries (country is ignored).

Further analysis:

When drilling down to show product detail, no value is shown for products that have been filtered out (not been selected).
When drilling down to show country detail, the same accumulated value is shown for both countries.

Example

If the filter (POV) parameter is omitted from Lookup, then all products are selected:

LOOKUP([MyAccount],,[d/Country])

Total value returned = 63$. That is, values for all products from all countries.

Further analysis:

When drilling down to show product detail, all values are available (no filter was applied).
When drilling down to show country detail, the same accumulated value for all products is shown for both countries.

Note

To maintain a consistent sign throughout the usage of the account dimension for account types INC and LEQ, the correct account type needs to be set.

For example, if you have an account with account type INC, the signs of the account values are automatically switched, as they are already stored with a reversed sign. If you create a calculated account that uses a Lookup formula based on that INC account, the calculated account should also be set to the INC account type if you want incomes to appear with the correct sign (usually positive).

For more information on account dimension attributes, see Attributes of an Account Dimension.