Assuming we have a set of data points in a Weekly Time Series for a measure – TRXUnits. The data below shows the weekly data for a given Product.

WeekName | TRXUnits |

Week1 | 123 |

Week2 | 345 |

Week3 | 146 |

Week4 | 234 |

Week5 | 156 |

Week6 | 198 |

Week7 | 212 |

Week8 | 200 |

Week9 | 178 |

Week10 | 167 |

Week11 | 199 |

Week12 | 170 |

Week13 | 211 |

The Line Chart for the above Table Data – with a Least Square Fit line is shown below.

The AWC calculation Strategy is given below. Assume that the starting time for the above data is called T_{0} and the final time is called T_{n}

- Assume that the equation of the least square fit line is – y = mx + C, where m is the slope of the line and C is the intercept.
- Get the values of y at Time T
_{0}and T_{n.}

Let these be : y_{n} = x_{n} * m + C &

y_{0} = x_{0} * m + C

AWC = ( y_{n} – y_{0} ) = m * (x_{n} – x_{0})

**Functional Requirements for Comptuing AWC**

This should be computable for any granularity for any dimension at any level.

For example – if we want to Calculate the AWC for the last 18 weeks of data here is the Calculated Measure – that is used to calculate the AWC in 2 steps.

The 1^{st} Calculated Member is used to calculate the Slope of the line using the build in function called – LinRegSlope. This Calculated Measure uses the RANK function.

<CalculatedMember dimension=”Measures”

formula=”LinRegSlope( {LastPeriods( 18, ([Period].[Week].Members.Item(18-1) ) ) }, [Measures].[TRx], Rank([Period].[Week].CurrentMember,

LastPeriods( 18, ([Period].[Week].Members.Item(18-1) ) ) ))”>

</CalculatedMember>

The 2^{nd} Calculated Member is then just used to multiply the Slope obtained from the 1^{st} calculated measure and multiply it by ( 18 -1 ) – which essentially is ( y_{n}-y_{0}) – as derived in the formula above.

<CalculatedMember dimension=”Measures” formula=” ( [Measures].[ Slope18Week] * (18-1) )”>

</CalculatedMember>

** MDX — Item function is Zero Based

**Sample MDX Queries using the above Summary Variable**

A sample MDX using the above Calculaated Measure would look like

select NON EMPTY {[Measures].[ AWC18] } ON COLUMNS,

NON EMPTY {[Alignment].[TerritoryName].Members} ON ROWS

from [Cube]

good work!