7 Replies Latest reply: Jun 20, 2013 10:43 AM by Jonathan Brough RSS

    Getting the total

      Hi All,

       

      I want to get the final total(ending balance) and move it for the next month (it will be the beginning balance)

       

      mbrsample.png

       

      the total 36,561,851.12 should go to April 2013 as the beginning balance and so fort.

       

      I hope you guys get it.

       

      Thanks in advance,

      Donna

        • Re: Getting the total
          Jonathan Brough

          You can use the before() function to look at the previous column in  a pivot table.

          You include the same expression within the brackets as you would use to display the sum in the Mar 2013 column. For example:

           

               before(sum(TransValue))

           

          Jonathan

          • Re: Getting the total

            bump

              • Re: Getting the total
                Jonathan Brough

                The before() function looks at the previous column in a pivot table. It needs to contain the same formula as the column you are searching within.

                See the reference manual page 818 (QV v11 manual), or perform a search for "before(". This says:

                 

                before([ total ] expression [ , offset [,n ]])

                Returns the value of expression evaluated with a pivot table's dimension values as they appear in the column

                before the current column within a row segment in the pivot table. This function returns NULL in all chart

                types except pivot tables.

                On the first column of a row segment a NULL value will be returned, as there is no column before this one.

                If the table is one-dimensional or if the expression is preceded by the total qualifier, the current row segment

                is always equal to the entire row.

                 

                Jonathan

              • Re: Getting the total

                bump

                • Re: Getting the total
                  Jonathan Brough

                  Say you have two dimensions, Product and month, and one expression, sum(SalesValue), within a pivot table where the second dimension of Month is pivoted so it runs across the top.

                  I think you can have a test to see if it is ColumnNo(1), and if not then add the contents of before(sum(SalesValue)) to the sum(SalesValue) for the current month that the column relates to. This could look something like this:

                   

                     if(ColumnNo()=1,sum(SalesValue),before(sum(SalesValue))+sum(SalesValue))

                   

                  Hope that works.

                  Jonathan