Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
bump
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
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.
Please see attached file!
bump!
bump!
bump again!
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
Alright! Thanks for you help Jonathan.
BUMP!