locked
Running Total RRS feed

  • Question

  • Hi Guys,

    Im trying to create a balance sheet using my AS cube. I need to create a running total month on month which i can do by using this:

    Sum({
                {[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
                [Date].[Calendar].CurrentMember
            }, [Measures].[Amount])

    However, when a new year starts i need the running total to start from 0 again, is there any easy way to do this?

    Example

                             2011                                          2012

                              1    2     3    4    5    6    etc       1     2     3

    Account Group   5    10  25   30  55   66               6    10   88

    Cheers

    Dave

    Wednesday, June 5, 2013 8:19 PM

Answers

  • Think i may have solved it:

    SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Amount])

    However, i'd be happy to hear any other way I could do this.....

    Cheers

    Dave

    • Marked as answer by Elvis Long Thursday, June 20, 2013 6:34 AM
    Wednesday, June 5, 2013 8:28 PM

All replies

  • Think i may have solved it:

    SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Amount])

    However, i'd be happy to hear any other way I could do this.....

    Cheers

    Dave

    • Marked as answer by Elvis Long Thursday, June 20, 2013 6:34 AM
    Wednesday, June 5, 2013 8:28 PM
  • That's exactly what you want.  

    It used to be that it was faster to write your own MDX to add up the days in the current period, then add up the earlier periods.  This uses fewer cells than a query that adds up all the days from start of year.  However, this does not appear to be the case any more, so your query is idea.

    For more information on recursive mdx see http://richardlees.blogspot.com.au/2008/11/recursive-mdx-doesnt-have-same-need-in.html

    Richard


    Richard

    Thursday, June 6, 2013 12:09 AM
  • Some time we need to do cumulative sum or running total in a table. In SQL we can easily do it. The following code will help us.

    SELECT      T1.SL,
                T1.GroupName,
                T1.Amount,
                SUM(T2.Amount) as CumulativeSum
    FROM  @Temp T1 INNER JOIN
                @Temp T2 on T1.SL >= T2.SL
    GROUP BY T1.SL,T1.GroupName, T1.Amount
    ORDER BY T1.SL

    for more with example you can visit: http://cybarlab.blogspot.com/2013/06/cumulative-sum-in-sql.html

    Hope it will help you.
    Thursday, June 6, 2013 5:51 AM
  • Thanks Richard,

    Perhaps you can help me with this. I need to create a calculated member which calculates a YTD value at one level (and children) in my hierarchy and then a running total at another level (and children).

    An example of the mdx which i cant get working:

    Iff([Account].[Accounts].&[1].Children = 'Balance Sheet', SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Amount]),
    Sum({
                {[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
                [Date].[Calendar].CurrentMember
            }, [Measures].[Amount]))

    Thursday, June 6, 2013 10:36 AM
  • Im looking to achieve:

    case
    when
     [Account].[Accounts].currentmember.membervalue.children = 'Balance Sheet' then
               
    Sum({{[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
                [Date].[Calendar].CurrentMember
                    }, [Measures].[Amount])
    When
     [Account].[Accounts].currentmember.membervalue.children = 'Net Income' then

    [Measures].[Amount]
     
    End

    Any help would be hugely appreciated.

    Regards

    Dave


    • Edited by Dave Kerby Thursday, June 6, 2013 12:36 PM
    Thursday, June 6, 2013 11:09 AM
  • Im almost there, can anyone assist with the last little bit. I need the calculation to be applied to all the decedents of the required level.

    I currently have.

    IIF
        ( [Account].[Accounts].CurrentMember.membervalue = "Assets"
       
         ,Sum({{[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
                [Date].[Calendar].CurrentMember
                    }, [Measures].[Amount])
         , [Measures].[Amount]
        )

    Thursday, June 6, 2013 12:50 PM
  • with member measures.[my calc] as
    case
    when
     [Account].[Accounts].currentmember.membervalue.children = 'Balance Sheet' then
    Sum({{[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
                [Date].[Calendar].CurrentMember
                    }, [Measures].[Amount])
    When
     [Account].[Accounts].currentmember.membervalue.children = 'Net Income' then
    Sum([Account].[Accounts].currentmember,[Measures].[Amount])
    else null
    end
    select measures.[my calc] on 0,
    nonempty( [Account].[Accounts].allmembers * [Date].[Calendar].allmembers, [Measures].[Amount]) on 1
    from [CubeName]

    Let me know if this works. I am not sure about the logic you placed in the first 'when .. then ' statement

    Regards, Anant

               
    Thursday, June 6, 2013 7:52 PM
  • Thanks Anant but this doesn't work.

    Im almost there with the following query but  can anyone assist with the last little bit. I need the calculation to be applied to all the decedents of the required level.

    I currently have.

    IIF
        ( [Account].[Accounts].CurrentMember.membervalue = "Assets"
       
         ,Sum({{[Date].[Calendar].CurrentMember.Level.Members}.Item(0):
                [Date].[Calendar].CurrentMember
                    }, [Measures].[Amount])
         , [Measures].[Amount]
        )

    Thursday, June 6, 2013 8:14 PM