November 16, 2005 at 1:55 am
Hi there
I am trying to do a linear regression based on the most recent 6 months that have data
This statement with the time period hard coded works:
LinRegPoint(
Rank([Time].[calendar].CurrentMember, [Time].[calendar].CurrentMember.Level.MEMBERS),
([Time].[calendar].[2005].[Quarter 1].[January]:[Time].[calendar].[2005].[Quarter 3].[September]),
[measures].[Units],
Rank([Time].[calendar].CurrentMember, [Time].[calendar].CurrentMember.Level.MEMBERS)
)
But if I try make it dynamic by picking up the latest month with data and showing the last 6 months like this, it returns err in the cube (the statement syntax passes though):
LinRegPoint
(
Rank
([Time].[calendar].CurrentMember, [Time].[calendar].CurrentMember.Level.MEMBERS),
Tail
(Filter([time].[calendar].[Month].members, Not IsEmpty([Time].[Calendar].CurrentMember)), 6),
[measures].[Units],
Rank
([Time].[calendar].CurrentMember, [Time].[calendar].CurrentMember.Level.MEMBERS)
)
Any ideas on what is wrong with my statement or alternate suggestions to get a linear regression dynamically based on the past six months would be much appreciated
November 16, 2005 at 8:44 am
Two things to try - put curly braces around your Tail function (even though it returns a set, it may want the 'set' braces); or alternatively try creating the set earlier in your statement and then using the set name in the LinRegPoint fn (e.g. use a WITH SET [myset] AS '<stmt here>' type statement).
Steve.
November 17, 2005 at 12:50 am
Hi Steve
I am trying to build it into a calculated measure - I couldn't figure out how to use the "with set" statement in a calculated measure - seems like this is not supported. (Correct me if I'm wrong)
Anyway, your suggestion lead me to building a named set in the cube and then referring to the named set in the formula in my calculated measure - this works
Thanks for the help
May 6, 2010 at 1:16 pm
Baddog,
Would you mind providing more specifics for how you made that work? I'm trying to do just what you did, and am also having issues.
Thanks,
May 6, 2010 at 1:24 pm
@Baddog - the intent was to create a set, not so much as part of the calc meaure but standalone, for use in (one or more) calc measures. Am glad to hear it worked, one thing to be aware of/careful of with named sets in the cube (versus in a query batch) is that the named set is basically static - it gets resolved when the cube is processed and then it doesn't change (you can test this with like a 'top 10'). A new property was added (I think in 2008) that allows for 'Dynamic' named sets, and these behave as I would assume they always should (but didn't) - that is, they can change.
Steve.
June 1, 2010 at 8:02 am
After many days of trial and error I was finally able to solve this problem. In my case I needed a least squares regression line for the previous 28 days. Here is my result in the hope that it will help someone else save a lot of time.
--Template
YesterdaysDateID =
VBA!DateDiff("d", '<Day 1 in the Date Dimension>', VBA![Date]())
--NOTE: this assumes the key is the day number, which is BTW the R Kimball recommended method
LINREGPOINT(RANK([<Date Dimension Name>].[<Desired Level Name>].CurrentMember,
LastPeriods(<No of Pds to go back>,
StrToMember('[<Date Dimension Name>].[<Desired Level Name>].&[' + VBA!Cstr([Measures].[YesterdaysDateID]) + ']'))),
LastPeriods(<No of Pds to go back>,
StrToMember('[<Date Dimension Name>].[<Desired Level Name>].&['+ VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']')),
[Measures].[<Desired Measure>],
RANK([<Date Dimension Name>].[<Desired Level Name>].CurrentMember,
LastPeriods(<No of Pds to go back>,
StrToMember('[<Date Dimension Name>].[<Desired Level Name>].&[' + VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']'))))
--Formula in SSAS for a LSRL that is for the last 28 complete days
YesterdaysDateID =
VBA!DateDiff("d", '1/1/06', VBA![Date]())
LINREGPOINT(RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&[' + VBA!Cstr([Measures].[YesterdaysDateID]) + ']'))),
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&['+ VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']')),
[Measures].[Work Orders],
RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&[' + VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']'))))
--Hardcoded equivalent for 6/1/10 with a dw for which Day 1 is 1/1/06:
LINREGPOINT(RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, [Creation Date].[Creation Date].&[1612])),
LastPeriods(28, [Creation Date].[Creation Date].&[1612]),
[Measures].[Work Orders],
RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, [Creation Date].[Creation Date].&[1612])))
--Test in SQL Server Management Studio as MDX Statement
WITH MEMBER [Measures].[YesterdaysDateID] AS
VBA!DateDiff("d", '1/1/06', VBA![Date]())
MEMBER [Measures].[OrderCountVariance] AS
LINREGPOINT(RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&[' + VBA!Cstr([Measures].[YesterdaysDateID]) + ']'))),
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&['+ VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']')),
[Measures].[Work Orders],
RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&[' + VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']'))))
SELECT [Measures].[OrderCountVariance] ON COLUMNS,
[Creation Date].[Creation Date].Members on rows
FROM [Work Orders]
--WHERE LastPeriods(-4, [Creation Date].[Creation Weeks Passed].&[1])
--Note the where clause should be something, although the above may not work for you;
otherwise if you have a lot of days and/or items to measure, the query may take awhile.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply