locked
weekly & monthly moving sum RRS feed

  • Question

  • Hi All,

    Could you tell me if there is the way to calculate weekly and monthly moving sum the same time. It should looks like this picture

    If you open any week : week column  should sum up customer count  by week, though month column should continue calculate from month  begging

    Actually I have tried to use (adWorksDW)

    sum([Date].[Day of Month].currentmember.lag(30):[Date].[Day of Month].currentmember,[Measures].[Customer Count])
    and
    sum([Date].[Day of Month].currentmember.lag(7):[Date].[Day of Month].currentmember,[Measures].[Customer Count])



    Thanks ahead
    Vlad



    Saturday, May 23, 2009 10:45 PM

Answers

  • Hi Akalya,

     
    Thanks for your help:
    month sum:

    iif

     

    ([Date].[Hierarchy 1].level is [Date].[Hierarchy 1].[Week of Year],null,SUM(periodstodate([Date].[Hierarchy 1].[Month of Year],[Date].[Hierarchy 1].currentmember),[Measures].[Customer Count]))

     

     week sum:

    SUM(periodstodate([Date].[Hierarchy 1].[Week of Year],[Date].[Hierarchy 1].currentmember),[Measures].[Customer Count])


    Thanks again
    Vlad

    • Marked as answer by vlad_22 Tuesday, May 26, 2009 3:04 PM
    Tuesday, May 26, 2009 2:05 PM

All replies

  • At a guess I would say that this is not working because you are using the [Day of Month] attribute which probably only has 31 members, therefore if you go .Lag(30) from day 5 it does not work. Try using an attribute based on the date that has a unique member for each date.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Monday, May 25, 2009 7:06 AM
  • Hi Darren,

    Thanks for your advise to use unique member ([date].[date]):

    sum([Date].[Date].currentmember.lag(31): [Date].[Date].currentmember,[Measures].[Customer Count])

    it works correctly for mothly culculation only, if I use calendarYear->MonthOfYear->WeekOfYear-> Date
    -------------------------------------------------------------------------------------------------------------------------------
    For week it will work if I chose calendarYear->MonthOfYear->WeekOfYear-> DayOfMonth

    sum
    ( [Date].[Day of Month].currentmember.lag(7) : [Date].[Day of Month].currentmember,[Measures].[Customer Count])

    ---------------------------------------------

    I wonder if you have an idea how combine them

    Thanks ahead,
    Vlad

    Monday, May 25, 2009 7:48 PM
  • I'm not sure what you mean by wanting to combine them. If you want create a pivot table like the image in your first post you would create these as 2 separate measures "31 Day Moving Sum" and "7 Day Moving Sum".
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Tuesday, May 26, 2009 3:39 AM
  • Hi Vlad,

    I am not clear as to why is there such a requirement. Can you give the actual business scenario on why you want to combine the weekly and monthly moving sum.

    But, I think it can be achieved as follows:

    you can use just one hierarchy calendarYear->MonthOfYear->WeekOfYear-> DayOfMonth. In this case, I assume that the week does not span across months. Here is the MDX for the calculated measure.

    (ancestor([Date].[ClaendarHier].currentmember, [Date].[CalendarHier].[Week]).prevmember,[Measures].[Customer Count]) + SUM(periodstodate([Date].[CalendarHier].[Week],[Date].[CalendarHier].currentmember),[Measures].[Customer Count])

    I'm not sure of the syntax. What I have tried is.. The first expression gets the customer count value for the previous week. i.e., in your example, for "Day of the month" as 5, it will get the customer count for the week number 31, which in this case is 17. The second expression adds this up with the week to date value of the customer count.

    Try this out.  It might help.

    -Akalya

    Tuesday, May 26, 2009 6:10 AM
  • Hi Akalya,

     
    Thanks for your help:
    month sum:

    iif

     

    ([Date].[Hierarchy 1].level is [Date].[Hierarchy 1].[Week of Year],null,SUM(periodstodate([Date].[Hierarchy 1].[Month of Year],[Date].[Hierarchy 1].currentmember),[Measures].[Customer Count]))

     

     week sum:

    SUM(periodstodate([Date].[Hierarchy 1].[Week of Year],[Date].[Hierarchy 1].currentmember),[Measures].[Customer Count])


    Thanks again
    Vlad

    • Marked as answer by vlad_22 Tuesday, May 26, 2009 3:04 PM
    Tuesday, May 26, 2009 2:05 PM