Exclude a member for MDX forecasting using linear regression

Question

I want to forecast measure value for the next month using data from complete previous months.

For example in this moment September 11, I have to forecast the value of the September month (cause the September month is not over) based on January-August values.

I am using MDX function LinRegPoint with the hierarchy detailed below

DimTime
Hierarchy Time
Year

Quarter

Month

This is the query I been trying without success:

WITH 
  MEMBER [Measures].[Trend] AS 
    LinRegPoint
    (
      Rank
      (
        [Time].[Hierarchy Time].CurrentMember
       ,[Time].[Hierarchy Time].CurrentMember.Level.MEMBERS
      )
     ,Descendants
      (
        [Time].[Hierarchy Time].[2015]
       ,[Time].[Hierarchy Time].CurrentMember.Level
      )
     ,[Measures].[Quality]
     ,Rank
      (
        [Time].[Hierarchy Time]
       ,[Time].[Hierarchy Time].Level.MEMBERS
      )
    ) 
SELECT 
  {
    [Measures].[Quality]
   ,[Measures].[Trend]
  } ON COLUMNS
 ,Descendants
  (
    [Time].[Hierarchy Time].[2015]
   ,[Time].[Hierarchy Time].[Month]
  ) ON ROWS
FROM [Cube];

The above query return all month forecasted values to today date including the September month, however It forecasts the September value taking the real values from January to September current date. I need the LinRegPoint function just takes the previous complete months in this case January to August.

Note the query returns a forecasted value for 9 month (September) but it is using the real value to calculate it. It would result in a misunderstood line as shown in the below images.

This image shows the drawn line taking the previous full-month (1-8):

Note the positive slope line.

This image shows the drawn line taking all months (1-9)

Note the negative slope line.

Question: How can I exclude the no complete current month from real values but allowing the forecasted value be calculated.

EDIT: The set should be changing to exclude the last month member in real values but calculating the forecasted value for it.

Thanks for considering my question.

SOLUTION:

WITH MEMBER [Measures].[Trend] AS 
  LinRegPoint
  (
    Rank(
        [Time].[Hierarchy Time].CurrentMember
       ,[Time].[Hierarchy Time].CurrentMember.Level.MEMBERS
        )
    ,Descendants
    (
        [Time].[Hierarchy Time].[2015]
       ,[Time].[Hierarchy Time].CurrentMember.Level
    )
    ,[Measures].[QltyNoCurrentVal]
    ,Rank(
        [Time].[Hierarchy Time]
       ,[Time].[Hierarchy Time].Level.MEMBERS)
    ),FORMAT_STRING='Standard' //Formating
MEMBER [Measures].[QltyNoCurrentVal] AS 
    IIF(
        [Time].[Hierarchy Time].CurrentMember is [Time].[Hierarchy Time].[2015].[3].[9]
       , NULL
       , [Measures].[Quality]
    ),FORMAT_STRING='Standard' //Formating
select {
    [Measures].[QltyNoCurrentVal]
   ,[Measures].[Trend]} ON COLUMNS
   ,Descendants(
        [Time].[Hierarchy Time].[2015]
       ,[Time].[Hierarchy Time].[Month]
   ) ON ROWS
FROM [Cube]

whytheq whytheq · Accepted Answer · 2015-09-11T14:23:34

Slightly confused which sets in your expression need changing.

I've created a set [MthsExclSept] which you should be able to use anywhere in the rest of your script - I've just used it in two places:

WITH 
  SET [MthsExclSept] AS 
    SubSet
    (
      Descendants
      (
        [Time].[Hierarchy Time].[2015]
       ,[Time].[Hierarchy Time].[Month]  //<<better to specify the month level
      )
     ,0
     ,
        Descendants
        (
          [Time].[Hierarchy Time].[2015]
         ,[Time].[Hierarchy Time].[Month]  //<<better to specify the month level
        ).Count
      - 1
    ) 
  MEMBER [Measures].[Trend] AS 
    LinRegPoint
    (
      Rank
      (
        [Time].[Hierarchy Time].CurrentMember
       ,[MthsExclSept] //<<also added here?
      )
     ,[MthsExclSept]
     ,[Measures].[Quality]
     ,Rank
      (
        [Time].[Hierarchy Time]
       ,[MthsExclSept] //<<also added here?
      )
    ) 
  MEMBER [Measures].[QualityNEW] AS 
    IIF(
        [Time].[Hierarchy Time].CurrentMember is [Time].[Hierarchy Time].[September]
       , NULL
       , [Measures].[Quality]
    )
SELECT 
  {
    [Measures].[QualityNEW]
   ,[Measures].[Trend]
  } ON COLUMNS
 ,[MthsExclSept] ON ROWS
FROM [Cube];

The set [MthsExclSept] just trims off the last month. Here is a proof via AdvWrks that trims of December from a year:

WITH 
  SET [MthsExclSept] AS 
    SubSet
    (
      Descendants
      (
        [Date].[Calendar].[Calendar Year].&[2009]
       ,[Date].[Calendar].[Month]
      )
     ,0
     ,
        Descendants
        (
          [Date].[Calendar].[Calendar Year].&[2009]
         ,[Date].[Calendar].[Month]
        ).Count
      - 1
    ) 
SELECT 
  {} ON 0
 ,[MthsExclSept] ON 1
FROM [Adventure Works];

It returns the following:

Exclude a member for MDX forecasting using linear regression

1 Answers