Sum Accross Years, Quarters, and Month [Time].[Year-Quarter-Month-Date] RRS feed

  • Question

  • How do I get to sum across Year, Quarter, and Month Time multi-select tree filter?

    NON EMPTY [Time].[Year -  Quarter -  Month -  Date].AllMembers
    ON ROWS,
    NON EMPTY { [Measures].[Result], [Measures].[Target], [Measures].[Previous Result] }
    FROM [OCParks]
    WHERE ( [MEASURE].[MEASURE NAME].&[Park Visitors] )

    My colleague used the following tuple formula and they did not work.

    aggregate({EXISTS([Time].[Month].[Month].Members,[FilterMonth]).Item(0):[Time].[Month].CurrentMember},[Measures].[MEASURE VALUE])

    sum({EXISTS([Time].[Month].[Month].Members,[FilterMonth]).Item(0):[Time].[Month].CurrentMember},[Measures].[MEASURE VALUE])

    Where do you insert you mdx query? In the KPI tuple formula or because its a dynamic filter on a scorecard to filter connection formula? My scorecard is connected to row and the source value is member unique name.

    ..see next message.

    Thursday, August 30, 2012 6:09 PM

All replies

  • This scorecard should of start with Previous Year Result Cumulative - July 1,804,418, Target Cumulative - July 7,106,385, and Result Cumulative - July 7,7207,586 then the next month Previous Year Result Cumulative August is July + August = 3,444,031, Target Cumulative - August + July = 3,616,233, Result Cumulative - August + July = 3,642,505 and this has to dynamically change based having the option to start by any particular month, quarter, or year.

    The sum must always start with the first time member selected.

    Any ideas?

    Thursday, August 30, 2012 6:10 PM
  • It would be best if you move all your calculations to your cube's calculated measures. What you looking for is a running total across all time periods. Here you will find couple of ways to do that:

    Mdx Running Total

    Running Total with Recursive MDX

    You might also need SCOPE since level of your time dimension change from day to week to month etc.

    Friday, August 31, 2012 1:59 PM