vendredi 11 septembre 2015

Exclude a member for MDX forecasting using linear regression

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.

enter image description here

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): enter image description here

Note the positive slope line.

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

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.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire