2 Replies Latest reply: Aug 7, 2012 5:33 AM by Sokkorn Cheav RSS

    Set Analysis on Previous Month

    Alfred Lee

      I have a simple table

       

      YearMonthMonthEnd Balance
      20121100
      20122200
      20123300
      20124400
      20125500
      20126600

       

      and I would need to display the data in the following format:

      YearMonthOpening BalanceMonthEnd Balance
      201210100
      20122100200
      20123200300
      20124300400
      20125400500
      20126500600

       

      I think this is to do with the Set Analysis but I do not know how to get the Opening Balance Expression correct.

      Please help and thanks in advance.

        • Re: Set Analysis on Previous Month
          Jonathan Dienst

          Hi

           

          Use this for opening balance in a chart like that in your post

           

          = Above[MonthEnd Balance]

           

          Regards

          Jonathan

          • Re: Set Analysis on Previous Month
            Sokkorn Cheav

            Hi Alfred,

             

            You can try Above([MonthEnd Balance]) in your expression.

            Procedure

            1. Load Data

            [Data]:
            LOAD * INLINE [
            Years,    Months,    MonthEnd Balance
            2012,    1,    100
            2012,    2,    200
            2012,    3,    300
            2012,    4,    400
            2012,    5,    500
            2012,    6,    600];
            

            2. Create Straight Table with:

                 a. Years and Months as Dimension

                 b. Two Expression

                      i. SUM([MonthEnd Balance])

                      ii. Above([MonthEnd Balance])

             

            See the sample attached file also.

             

            Regards,

            Sokkorn