SQL Server Performance

3 month to date average

Discussion in 'SQL Server 2005 Analysis Services' started by nsomani1, Mar 31, 2008.

  1. nsomani1 New Member

    Friends i have a very unusual situation , please help me out
    i have requirment , which should give the average of 3 months prior ,
    when i use lag or last periods command option, it does calculate average on month basis, but i need to calculate average on day wise
    for example if i am on 15th day of the month , it should calculate the average on last two months and 15 days which should be average of 75days
    Average on 30days(1st Month)+ 31days(2month)+ 15 days (of current month)
    but apparently lag either takes me at month level or day level. i need to have 3month average on a day level
    currently i am using command ,
    AVG([Tbl Date Key].[Report Hirerachy].CurrentMember.Lag(2):[Tbl Date Key].[Report Hirerachy].CurrentMember,[Measures].[Q1_Avg]
    )

    but that takes me lag on 2 month not a date wise. if i use a lag of 90 days , it takes 90 days behind but not average of 75 days as per scenario
    please suggest
  2. FrankKalis Moderator

    This is one one the very rare situations where you might consider using a cursor. Search this site for an article by Mirko Marovic. He wrote about that some time ago.
  3. nsomani1 New Member

    The Article of Mirko Marovic is on T-SQL Cursor. I have requirment that i need to take care of in MDX
  4. FrankKalis Moderator

    Oops, pardon, I missed that you posted in the Analysis Server forum...

Share This Page