MDX LinRegPoint Function
-
Hi all, I'm trying to perform a simple trend predict on a time series of financial statement line items. Having some problems returning values;
The BOL sections for LinRegSlope and LinRegIntercept state that "If the second <<Numeric Expression>> is not present, the function uses the members of <<Set>> as values for the x-axis... it is often used with the Time dimension." The BOL section for LinRegPoint states "linregpoint uses it's last three arguments like the other linregxxx functions...". Thus, I dropped the last argument from LinRegPoint expecting to use the time series from the set argument (the example looks like just such a case). I can only return #ERR (adding in a 4th argument, another numeric expression, works fine but naturally the user will not accept that). MSKB #30276 is the only other reference I can find, and it uses all 4 arguments.
I must be missing something... Can anybody enlighten me on proper usage of this function??
-
Geez I hate maths....
I got it to work but not being overly into stats, I can't say that this is actually going to help....Anyways, here's the code, a simple example from Foodmart. I used a static value of '2' for the x value in the y = ax + b, not sure if this is what you wanted. Note also the SOLVE_ORDER statement for the calc member, not having this value definitely makes a difference in the return values, in particular, if you have other calc members in the measures dimension, these will potentially solve before/after this calc member.
WITH MEMBER
[Time].[1997].[Q1].[bob] AS
'LinRegPoint(2,{[Time].[1997].[Q1].Children},[Measures].[Store Sales])', SOLVE_ORDER = 2
SELECT
ADDCALCULATEDMEMBERS({[Time].[1997].[Q1].children}) on 0,
ADDCALCULATEDMEMBERS({[Measures].Members}) on 1
FROM Sales
I hope this helps... Can you predict future stock values now? 😛
Also, for anyone else maybe wanting to check that KB article, its 307267 not 30267 as noted above.
Steve
Edited by - stevefromOZ on 08/06/2003 07:06:36 AM
Steve.
-
Thanks for replying! "Ooops" on the knowledge base # 😉 Thanks for the feedback, the example does return values... However, they're not what I expected. The user is reconciling my numbers by testing against the Excel trend function (swears this is the same thing). This does give me some more avenues to explore anyway! Thanks again for replying!
-
I think I have it sussed, I finally got it to return *exactly* the same result as Excel. I think the problem (re: the last param inclusion or noninclusion) relates to one little line in the BOL
quote:
Note Empty cells or cells containing text or logical values are ignored; however, cells with values of zero are included.. So, in my testing I was using a time dimension (foodmart sales) as suggested is a good option for leaving out the last param BUT because the time dimension is returning strings (not numbers), these values are essentially being ignored for use as the x-values. SO to get the same result as the excel 'TREND' fn, I created a simple calc member that interrogated the [Time].CurrentMember, looked to see if it was Month 1, 2 or 3 in Q1 of 1997, if it was, then the measure value was a 1, 2 or 3. I then used this measure as the last param in the function, and sure enough, period/month 4 was predicted and displayed the same value as Excel's prediction.
I can send through the MDX if this will help. So, I think it is related to the exclusion of text items as valid values for the last param. This is really a flaw in the functions methinks! Should possibly be added to the Newsgroups as a Qn to MSft.
Steve
Steve.
-
quote:
I hope this helps... Can you predict future stock values now? 😛
if you can, I'll organize the money and we'll meet in Nassau or on the Caymans'.Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks all for the feedback! Sorry for the late response, went on vacation right after I presented the solution. BTW, I got the solution from a 7/13/03 post by one Haiwei Xu. It seems you need to fake out the function by providing a "parrallel" measure as follows:
with
member measures.x
as 'rank( [time], descendants( [time].[1997], [month] ) )'
member measures.trend
as 'linregpoint(x, descendants( [time].[1997], [month] ), measures.[unit sales], x)'
select
{ measures.x, measures.trend } on columns,
{ descendants( [time].[1997], [month] ) } on rows
from sales
This will map to an excel reconciliation. I modified it to use lastperiods() so I could pass in varying lengths of time (since trend over the last 3 years typically has a much different slope than one over the last 5) from the current period.
if you can, I'll organize the money and we'll meet in Nassau or on the Caymans'.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply