Returns the 7avg (mean of previous seven rows) as a percentage of the sum of all 7avg values
The first 7 rows are returned as NULL
The sum of all 7day values is calculated by adding together the 7day values for each row in the resultset.
Purpose
Use this function to:
- Calculate moving average as a percentage of total
Calculation
7avg% = 7avg(X) / Sum(7avg(N:0))
RowX = value for current row
7avg(X)= 7avg for current row
Sum(7avg(N:0))= sum of 7avg for each row in resultset
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 | 7avg% |
| 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
7avg% will change depending on the number of rows that are returned in the result set.
See Multi-Function Profile / Options for details of how to control the number of rows in a result set
See 7avg for more information on rolling averages.
Example
- Example use: 7 day average sales as a percent of Total sales
NOTE: Measures 1 and 2 are included purely for illustration. It is not necessary to include these measure for the calculation to work.
Dimension: [transactions].[date]
Measure 1
- Name: = Sum
- Measure: = transactions.castprice
- Function: = Sum
- Post: =
- Value: =
- Axis: =0
- Plot:=Y
- Resolve:=
- Series Type:=Bar
- Popup:=Default
- Prefix:=
Measure 2
- Name: = Rolling 7 day average
- Measure: = transactions.castprice
- Function: = Sum
- Post: = 7avg
- Value: =
- Axis: =0
- Plot:=Y
- Resolve:=
- Series Type:=Line
- Popup:=Default
- Prefix:=
Measure 3
- Name: = % of total
- Measure: = transactions.castprice
- Function: = Sum
- Post: = 7avg%
- Value: =
- Axis: =1
- Plot:=Y
- Resolve:=
- Series Type:=Line
- Popup:=Default
- Prefix:=
Options: Row Count = 30
Sort Order: [Ascending Label]
