@SPLINE

Applies a smoothing spline to a set of data points. A spline is a mathematical curve that smoothes or interpolates data.

Syntax

@SPLINE (YmbrName [, s [, XmbrName [, XrangeList]]])
Parameter Description

YmbrName

A valid single member name that contains the dependent variable values used (when crossed with rangeList) to construct the spline.

s

Optional. A zero (0) or positive value that determines the smoothness parameter. The default value is 1.0.

XmbrName

Optional. A valid single member name that contains the independent variable values used (when crossed with rangeList) to construct the spline. The default independent variable values are 0,1,2,3, and so on.

XrangeList

Optional. A valid member name, a comma-delimited list of member names, cross dimension members, or a member set function or range function (including @XRANGE) that returns a list of members from the same dimension. If XrangeList is not specified, Essbase uses the level 0 members from the dimension tagged as Time.

Notes

  • XrangeList must contain at least two values.

  • If XrangeList contains gaps in the data (for example: Jan, Feb, Mar, Jun, Jul), be sure to specify XmbrName (for example: 0,1,2,5,6) so that correct results are returned.

  • The @SPLINE function skips #MISSING values in YmbrName and XmbrName; in the result, Essbase replaces the #MISSING values of YmbrName with the spline values.

  • The @SPLINE function calculates a smoothing cubic spline for (n > 0).

  • Setting the smoothness parameter (s) to 0 produces an interpolating spline, that is, a spline that fits the initial data exactly. Increasing s results in a smoother spline but a less exact approximation of the initial data.

  • The @SPLINE function can be used with the @TREND function to forecast future values that are based on the values smoothed with @SPLINE.

  • If you use an Essbase member set function to generate a member list for the XrangeList parameter (for example, @SIBLINGS), to ensure correct results, consider the order in which Essbase sorts the generated member list. For more information, see the help topic for the member set function you are using.

  • When you use @SPLINE in a calculation script, use it within a FIX statement. Although using FIX is not required, it may improve calculation performance.

  • When you use @SPLINE across a large range in a sparse dimension, you may need to increase the size of the calculator cache. For more information on the calculator cache, see the Oracle Essbase Database Administrator's Guide.

  • View the Algorithm for the smoothing spline.

Algorithm

Smoothing spline algorithm, part one.
Smoothing spline algorithm, part two.
Smoothing spline algorithm, part three.
Smoothing spline algorithm, part four.
Smoothing spline algorithm, part five.

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Spline. The formula calculates the spline of Sales values for Jan through Jun, based on a smoothness parameter of 2.

"Sales Spline" = @SPLINE(Sales,2,,Jan:Jun);

This example produces the following report:

             Colas     Actual    New York
               Sales       Sales Spline
               =====       ============
Jan             645         632.8941564
Feb             675         675.8247101
Mar             712         724.7394598
Apr             756         784.2860765
May             890         852.4398456
Jun             912         919.8157517

See Also