Calculated Measure and MDX to calculate Average Weekly Change (AWC) for Time Series in Mondrian

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 T0 and the final time is called Tn

  1. 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.
  2. Get the values of  y at Time T0 and  Tn.

Let these be :                                       yn = xn * m + C           &

y0 = x0 * m + C

AWC = ( yn – y0 ) = m * (xn – x0)

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 1st 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 2nd Calculated Member is then just used to multiply the Slope obtained from the 1st calculated measure and multiply it by ( 18 -1 ) – which essentially is ( yn-y0) – 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]

Advertisements

One response to “Calculated Measure and MDX to calculate Average Weekly Change (AWC) for Time Series in Mondrian

  1. good work!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s