@SHIFT

The @SHIFT calculation function for Essbase returns either the prior or next n^th cell value from mbrName, in the sequence XrangeList, retaining all other members identical to the current member.

The direction of @SHIFT is wholly based on n, with positive n values producing an effect equivalent to @NEXT and negative values of n producing an equivalent effect to @PRIOR.

Syntax

@SHIFT (mbrName [,n, XrangeList])

Parameters

mbrName

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

n

Optional signed integer. Using a negative value for n has the same effect as using a positive value in the @PRIOR function. n must be a numeric value, not a reference, such as a member name.

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.

Notes

@SHIFT is provided as a more appropriate, self-documenting name than @NEXT or @PRIOR when the value for n is a variable and may change from positive to negative, depending on the database state when the call occurs (that is, when the usage is likely to be NEXT and/or PRIOR).

Example

In this example, Prev Asset for each month is derived by taking the Asset value from the previous month because -1 is specified as the n parameter. Next Avl Asset for each month is derived by taking the Asset value from two months following the current month because 2 is specified as the n parameter. Since the range sequence is not specified for either formula, the level 0 members from the dimension tagged as Time are used.

"Prev Asset" = @SHIFT(Asset,-1);
"Next Avl Asset" = @SHIFT(Asset,2);

This example produces the following report:

                 Jan    Feb    Mar    Apr    May    Jun
                 ===    ===    ===    ===    ===    ===                    
Asset            100    110    105    120    115    125
Prev Asset       #MI    100    110    105    120    115
Next Avl Asset   105    120    115    125    #MI    #MI

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

FIX("West")
"Prev Sales" = @SHIFT(Sales, -1, @XRANGE("2011"->"Sep", "2012"->"Mar"));
ENDFIX;

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


2011->Sep
2011->Oct
2011->Nov
2011->Dec
2012->Jan
2012->Feb
2012->Mar