Following forecast member returns forecast figures for a given measure based on the entire history.
WITH MEMBER
[Measures Utility].[Measure Calculations].[Linear Regression Forecast]
AS LinRegPoint(
Rank(
[Period].[Extended Year - Quarter - Month].CurrentMember,
[Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members
),
Exists(
[Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members,
[Period].[Calendar Year - Quarter - Month].[Month].Members),
([Measures].CurrentMember, [Measures Utility].[Measure Calculations].DefaultMember),
Rank(
[Period].[Extended Year - Quarter - Month].CurrentMember,
[Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members)
)
SELECT
([Measures].[RXA Value] *
[Measures Utility].[Measure Calculations].[Linear Regression Forecast]
) on 0,
NON EMPTY {[Period].[Extended Month].Children}
ON 1
FROM [Cube Name]
Forecasts based on 3 months data points
CREATE MEMBER CURRENTCUBE.[Measures Utility].[Measure Calculations].[Linear Regression Forecast - 3 Data Points]
AS LinRegPoint(
Rank([Period].[Operational Year - Quarter - Month].CurrentMember,
{
Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),1).Item(0).Item(0).Lag(2):
Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),1).Item(0).Item(0).Parent.Parent.LastChild.LastChild
} --[Output Period]
), -- Measures.OutputX (Current Financial Year)
Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),3), -- [Input Period] -- 3 Months
( [Measures].CurrentMember,
[Measures Utility].[Measure Calculations].DefaultMember), -- Measures.Input Y
Rank(
[Period].[Operational Year - Quarter - Month].CurrentMember,
Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),3) --[Input Period] -- 3 Months
) -- Measures.InputX
),
FORMAT_STRING = "#,#.00",
VISIBLE = 1;
No comments:
Post a Comment