LinRegPoint

Question

We're trying to create a measure using the LinRegPoint function within our measures table in Database Engine.   Here's the code I've written, but we're not getting any results.  I'm hoping someone with more knowledge on MDX than I can help me debug this.

This is a measure within the SAP BPC application which uses SQL Server 2005.

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC" OR [%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",IIF([%TIMEDIM%].Currentmember.Level.Ordinal = [%TIMEDIM%].[Month].Ordinal,LinRegPoint(7,LASTPERIODS(6, [%TIMEDIM%].Currentmember),[Measures].[12MOAVG]), MEASURES.[12MOAVG]), MEASURES.[12MOAVG])

Answer 1

Consider posting this to some Analysis Server/BI forum, you are more likely to get help there. :-)

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 2

".. This is a measure within the SAP BPC application which uses SQL Server 2005 .." - but is the app using Analysis Services 2005, or just SQL 2005  relational with a different OLAP/MDX  engine? In the case of AS 2005, this paper should help:

Using Linear Regression MDX functions for forecasting


Based on the guidance in the paper, you can add a 4th parameter for LinRegPoint(), and change it to:

LinRegPoint(7, LASTPERIODS(6, [%TIMEDIM%].Currentmember), [Measures].[12MOAVG],
Rank([%TIMEDIM%].Currentmember, LASTPERIODS(6, [%TIMEDIM%].Currentmember)))

---

- Deepak