SSAS 2012 - LinRegPoint help

• Terry300577

  Hall of Fame

  Points: 3391

  More actions

  June 20, 2013 at 1:00 am

  #295473

  I am trying to do some linear regression analysis in my cube so that i can the use this to forecast future revenue. I have written all the calculation script and deployed it successfully but there are no results being returned and i cannot figure out where i have gone wrong.

  I'm hoping someone here can help me before i go mad staring at the code, i am pretty sure it is something to do with how i have set-up the Rolling 12 Months Set but cant see the problem for looking..

  Additionally I only have data spanning back to august last year if that makes any difference to how the results are produced

  Here's the code.

  CREATE DYNAMIC SET CURRENTCUBE.[Rolling 12 Months] AS

  {strtomember("[DIM Date].[Date].[" +

  cstr(year(dateadd('yyyy',-1,now()))) + "-" +

  Right('0'+ cstr(Month(now())),2) + "-" +

  Right('0'+ cstr(Day(now())),2)+

  "]")

  :strtomember("[DIM Date].[Date].[" +

  cstr(year(dateadd('yyyy',-0,now()))) + "-" +

  Right('0'+ cstr(Month(now())),2) + "-" +

  Right('0'+ cstr(Day(now())),2)+

  "]")};

  CREATE MEMBER CURRENTCUBE.[Measures].[Rank 12 Months] AS

  RANK([DIM Date].[Year Month Key].CurrentMember

  , ORDER([Rolling 12 Months]

  , [Measures].[Month Key]

  , BASC )

  ), VISIBLE = 0;

  CREATE MEMBER CURRENTCUBE.[Measures].[12 Month Trend] AS

  iif( [Measures].[Completed Days] <27

  ,null

  ,LinRegPoint([Measures].[Rank 12 Months]

  ,[Rolling 12 Months]

  ,[Measures].[Sales Amount]

  ,[Measures].[Rank 12 Months]

  )

  )

  , FORMAT_STRING = "Currency"

  , VISIBLE = 1

  , ASSOCIATED_MEASURE_GROUP = 'Sales Measure' ;

  Thanks in advance for any help or advice anyone can give me.