Returns the nth cell value in the sequence rangeList from mbrName, retaining all other members identical to the current member.
@SHIFT (mbrName [,n, rangeList])
mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination. |
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. |
rangeList | Optional. A valid member name, a comma-delimited list of member names, member set functions, and range functions. If rangeList is not specified, Analytic Services uses the level 0 members from the dimension tagged as Time. |
This function gets either the prior or next (nth past or future value in rangeList) value of mbrName, based on n. 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.
@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).
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 rangeList 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
@MDSHIFT
@NEXT
@PRIOR
@SHIFTPLUS
@SHIFTMINUS
©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |