Question on Pivot Table

[#1]

Hi All!

I have a question regarding pivot table. I have a table that looks like this:

Now the totals : 1000, 140 and 700 (background is red) should be in the Beginning balance value under the Month Feb-2014

So, I have an excel file which contains only the beginning balance for Jan-2014, The total that is generated from Jan-2014 or the Ending balance should be placed on the Beginning Balance of Feb-2014 and so on. (For example, Feb-2014 ending balance will now go to Mar-2014 beginning balance)

How do I achieve this? Is it possible on Pivot table?

Thanks in advance,

Donna

[#2]

bump

[jonathandienst]

Hi

You should be able to use Before(...) or Before(TOTAL ...) to get the previous closing total, but you will have to create a single expression that returns same values as the total line. If you are not sure how to do that, I suggest you post a sample qvw so that it can be looked at in more detail.

HTH

Jonathan

[#4]

I'll try to provide you with a dummy data. But If you do have a sample qvd that can give me the initial idea how to do it. It will be very helpful.

[#5]

Please see attached file!

[#6]

bump!

[#7]

bump!

[#8]

bump again!

[jonathandienst]

I had a look at your sample. On reflection it would be better to do this in script. I have not got time at the moment to assist in more detail, but I would add a resident load to load the previous periods closing balance into the AMOUNT field. Then your pivot will work properly.

Sorry I cannot spend more time on this right now. Perhaps someone else can pick this up.

Jonathan

[#10]

Alright! Thanks for you help Jonathan.

BUMP!