Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Using Linear Regression to Calculate Growth

with 2 comments

A few blog entries back I showed the MDX I used to calculate a seasonally-adjusted total in my chalk talk at the BI conference. This is useful but if we’re looking for a calculation that we can use for the Trend property of a KPI it’s not the whole story – we still need to find a way of expressing how much a value is growing or shrinking over time. Although previous period growth calculations are a lot more useful with seasonally-adjusted values, we can use simple linear regression (and it has to be simple because, as I said, I’m no statistician) to do a better job.

The starting point for understanding how to use linear regression in MDX is (surprise, surprise) Mosha’s blog entry on the subject:

http://sqljunkies.com/WebLog/mosha/archive/2004/12/21/5689.aspx

However, the function that’s going to be most useful here is the linregslope function. If we’re looking at the values in our time series and trying to find a line of best fit for those values with the equation y=ax+b, linregslope returns the value of a in that equation, ie the gradient – when the value of x increases by 1, y increases by the value of a. Here’s an example of how to use it:

with member measures.gradient as
linregslope(
lastperiods(3, [Date].[Calendar].currentmember) as last3
, [Measures].[Internet Sales Amount]
,rank([Date].[Calendar].currentmember, last3)
)

select {[Measures].[Internet Sales Amount], measures.gradient} on 0,
[Date].[Calendar].[Month].members on 1
from [Adventure Works]

The trick with using this function in MDX with a time series is to be able to work out what values you want to pass in for the x axis. Here I’ve used the lastperiods function to get a set containing the current member on the Calendar hierarchy, the previous member on the Calendar hierarchy and the member before that, in the first parameter of the function; at the same time I’ve declared a named set and then used that with a rank function in the third parameter to return the values 1, 2 and 3 for each of these three members.

This gets us the slope, then, but I was thinking it would be better to express this value as a percentage – but of what? The current period’s value? Or one of the preceding two periods values? I have to admit I don’t know which would be correct. Can someone help me out here? Please leave a comment..

Written by Chris Webb

May 19, 2007 at 12:01 am

Posted in MDX

2 Responses

Subscribe to comments with RSS.

  1. Business speak is typically annual growth, so the trailing 12 periods, how much have I grown in the last 12 months?

    Steve

    May 26, 2009 at 11:49 pm

  2. I’ve started a thread on my efforts to use MDX to calculate Linear Regression for SSRS on the MSDN SSRS Forum:

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/12e5a000-1b9c-436c-90a5-c62b0da06d8b

    Mike Honey

    November 8, 2012 at 12:52 am


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

Follow

Get every new post delivered to your Inbox.

Join 3,296 other followers

%d bloggers like this: