@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 Oracle Essbase Technical Reference 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.
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.8157517See Also