delta7avg
- 1 Minute to read
- Print
- DarkLight
- PDF
delta7avg
- 1 Minute to read
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
Returns the difference in 7 row average between current row and previous row
- Calculates the average for the previous seven rows
- Calculates average for current row and previous 6 rows
- Calculates difference between the 2 averages
The first 7 rows are returned as NULL
Purpose
Use this function to:
- Calculate daily change in 7 day average
Calculation
delta7avg = 7avg(x) - 7avg(x-1)
7avg(x) = Sum([X-1]:[X-7]) / 7
7avg(x-1) = Sum([X]:[X-6]) / 7
Sum([X-1]:[X-7])= Sum of previous 7 rows
Sum([X]:[X-6])= sum of current row and previous 6 rows
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 | delta7avg |
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
See 7avg and 7avg(percent) for more information on Rolling averages
Example
- Example use: Daily change in 7 day average
NOTE: Measure 1 is included purely for illustration. It is not necessary to include this measure for the calculation to work.
Dimension: [transactions].[ywoy]
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: = delta 7 avg
- Measure: = transactions.castprice
- Function: = Sum
- Post: = delta7avg
- Value: =
- Axis: =0
- Plot:=Y
- Resolve:=
- Series Type:=Line
- Popup:=Default
- Prefix:=
Options: Row Count = 30
Sort Order: [Ascending Label]
Was this article helpful?