Excel 2007 : Pivot Tables

**vturrientes** · 06-22-2011, 06:12 AM

I have a pivot table in an Excel 2007 which contains information about debts. On the left side, the table shows states and their villages. When these data are coalesced or expanded the debts in the pivot table are shown in the correct way since the state´s debt is the addition of all its villages’ debts.
At the top of the table I need to put information about when debts occur. I want to put three levels: year, quarter and month. But in this case when the information is coalesced o expanded it isn´t showed in a correct way because the year debt is equal to December´s debt, a quarter´s debt is equal to last month of the quarter´s debt.
I can´t use none accumulative predefined functions because this behavior can´t be implemented not with sum, nor with max,…..

How can I simulate this behavior?
Thank in advance.

**Roger Govier** · 06-22-2011, 06:15 AM

Hi

Welcome to the forum

Can you post a sample of your data.

**vturrientes** · 06-28-2011, 03:01 AM

I attach my Excel and try to explain my problem again.

My problem is that I get the data from a database witch stores the data monthly. So my quarter´s data is the data of last month`s quarter, and year´s data are the December´s data.

But in Excel I don’t know as I can’t select a data using the level of my navigation element selected (year quarter o months) and not to use an aggregate function like Excel uses.

Thank again.

**Roger Govier** · 06-28-2011, 07:00 AM

Hi

I see your problem.
I don't think you can achieve what you want in a single PT, as when you condense the 3 months making up a Quarter, it will automatically accumulate the data.

I would add an extra field to your data source called Months, and just have that show the Month of the row without any year.
I would then only have the Trimestre name show against Months 3, 6, 9 and 12

Then Have 2 PT's based upon the same source, one which shows all months of the year and the second which only shows the Trimestres (Quarters). You could have the second report still using Months rather than Trimestres as your field, but only selecting 3,6,9 and 12.

If you can accept VBA, then I have shown a Button on the Monthly Report sheet which will hide Months which are not Quarter end, and a button to show them again.

**vturrientes** · 06-29-2011, 03:39 AM

I attach a file to try to explain in other way my problem.

Thank.

**Roger Govier** · 06-29-2011, 07:27 PM

Hi

I understood your problem exactly, and that is what my solution addressed.

How is Excel supposed to know that the figure at the end of month 3 is the figure that you want, as it is the outstanding value at that point in time?
The NORMAL thing would always be to expect values to be the Summation of values from 3 months to make up 1 Quarter.

Your requirement is totally outside the scope of what any Pivot Table calculation to do.

The solution is to have a report set out in the way that I did, with macros, so you can select to see all 12 months,only the 4 "Quarter" months, and, although I didn't show it, just the 12th Month for the position at the end of the year.

There is no other way to achieve what you want with Pivot Tables, other than the solutions I have given.

**vturrientes** · 07-01-2011, 02:59 AM

Please, could you send me an example which shows the solution you have proposed?

Thanks in advance.

**Roger Govier** · 07-01-2011, 04:46 AM

Originally Posted by vturrientes

Please, could you send me an example which shows the solution you have proposed?

Thanks in advance.

Hi

I do apologise, I thought that I had attached the file to my original posting.
Without it, what I was saying would not really have made much sense.
If you have any further queries, please post back.