2d12lift0
(A - B) divided by B, where B is 12 rows prior to A.
- Calculates ratio of Row(X) -Row(X-12) (12)
- Divides ratio by Row(X-12) (lift)
- Resets when dimension 1 changes. (2d)
- Result of first 12 rows in the sequence are 0. (0)
Purpose
Use this function to:
- Calculate year on year lift when one of the dimensions is in months
Calculation
d12lift0= Ratio[x/(x-12)] / Row[X-12]
Ratio[x/(x-12)] = Row(X) / Row(X-12)
Row(X)= value for current row
Row(X-12) = Value for row 12 rows prior
Sample Result
Parameters
| Parameter | Description |
|---|---|
| Dimensions | Discrete field(s). All fields must come from the same table. |
| Name | Name to be displayed as Column Header in result grid |
| Measure | The field to be used in the post-function calculation.
|
| Function | COUNT / SUM / AVG / MIN / MAX / STDEV |
| Post | 2d12lift0 |
| Value | Optional. Filter to apply when evaluating FUNCTION. Only records in the Filter Recordset will be included in the Function and Post-Function calculations. Must come from same table as DIMENSION/MEASURE or be linked to the Dimension table. |
| Axis | 0 / 1. Y-Axis to use when plotting graph. Default = 0 |
| Plot | Y / N. Specifies whether measure’s output column should be plotted on graph. Default = Y |
| Resolve | Optional. Resolve level for the measure. If blank, Resolve = Dimension Table |
| Series Type | Default / Bar / Stacked Bar / Line / Spline / RangeLow / RangeHigh / CSLow / CSHigh / CSOpen / CSClose Determines the graph style for the measure. |
| Popup | Default / None / Extended |
| Prefix | $ / £ / E / % |
Usage Notes
Useful for year on year calculations when one of the dimensions is in months.
Sort order is important - Ascending / Descending Label / Value.
Example
NOTE: Measure 1 is included purely for illustration. It is not necessary to include this measure for the calculation to work.
Example use: Year on Year calculation of Sales by Product Group
Dimension: [transaction].[product_group], [transaction].[yearmonth]
Measure 1
| Measure 2
|
