Getting the total

[#1]

Hi All,

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

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

[Anonymous]

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

[#3]

bump

[#4]

thank you for the reply. can you elaborate further? thank you.

[Anonymous]

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

[#6]

bump

[#7]

doing this, I only need to get the before total value and put it on the row of beginning balance. is it possible to do that, i mean, the total value will only be located on the row of beginning balance, as the other type, values will be from the database

[Anonymous]

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