@SHIFTMINUS

The @SHIFTMINUS calculation function for Essbase returns a cell value at a given distance from a member name within a range. It can optimize performance over using @SHIFT, @PRIOR, or @NEXT, for certain formula patterns.

This function can be used in place of @SHIFT, @PRIOR, or @NEXT to improve performance if the formula meets the following criteria:

The formula is being executed in CELL mode.

The formula has one of the following patterns:

X = Y - @SHIFT(mbrName [,n, XrangeList])

or:

X = Y - @PRIOR(mbrName [,n, XrangeList])

or:

X = Y - @NEXT(mbrName [,n, XrangeList])

If these criteria are met, consider rewriting your formula using @SHIFTMINUS, which runs the formula in block mode to improve performance.

Note: If you use this function in combination with a function that runs in cell mode, it may necessitate execution in cell mode to resolve dependencies. To determine whether a formula executed in cell mode, check the log for the following informational message: Formula for member [mbrName] will be executed in [CELL] mode. To learn which functions use cell mode, see the @CALCMODE topic.

Syntax

@SHIFTMINUS (mbrName1, mbrName2 [,n, XrangeList])

Parameters

mbrName1

Any valid single member name, or a function that returns a single member.

mbrName2

Any valid single member name, or a function that returns a single member.

n

Optional signed integer. n must be a numeric value, not a reference, such as a member name. If you are using @SHIFTMINUS to replace the @NEXT function, use 1 as the value for n. If you are using @SHIFTMINUS to replace the @PRIOR function, use -1 as the value for n. Default value is +1.

XrangeList

Optional parameter specifying a sequential range of members. If a range is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Can be a valid member name, a comma-delimited list of member names, cross dimensional members, or a return value from a member set function or range function (including @XRANGE).

For more information about rangeList and XrangeList, see Range List Parameters in the topic Range and Financial Functions.

Example

The following example shows a formula using @SHIFT().

Sales = Loss - @SHIFT(Sales, 1);

Here is the formula using @SHIFTMINUS() to improve performance:

@SHIFTMINUS (Loss, Sales, 1)

The following example assumes a Year dimension is added to Sample Basic.

FIX("South", "East")
Sales = @SHIFTMINUS (COGS, Sales, 1, @XRANGE("2018"->"Sep", "2019"->"Mar"));
ENDFIX;

The above calculation is performed across the following multidimensional range specified by XrangeList:


2018->Sep
2018->Oct
2018->Nov
2018->Dec
2019->Jan
2019->Feb
2019->Mar