Pivot table Help

[nandhamnk]

Hi All,

Attached Report Showing Revenue,Cost and profit.This report is used to prepare on Monthly basis.Based on Workings i work out pivot Table where i show the report.

Now i need to show Cummulative profit in a separate column every month i.e When i work out Nov Statement ,it should include october & November profit,when i prepare Dec Statement it should include October+november+December Profit.

Anybody help me out

Thanks in Advance

MNK

[nandhamnk]

Can anybody help me out

Thanks

[nandhamnk]

Is this Possibe !!!!!!!!!!!!!!!!!!

[MSP77079]

I worked on this on and off today for a couple of hours. It seems difficult.

If the data table had profit in it, that would make life easier. But, since Profit is already a calculated field .... Let's just say, I would know how to do it in Access, but it would take a subquery ... which is not something pivot tables support (as far as I know).

On the other hand, I am NOT a great authority on Pivot Tables.

So, basically, what I am thinking is that you need a formula next to the table that does a SUMIF for revenue and subtracts a SUMIF for each of the costs.

[MSP77079]

Strike that ... can't use SUMIF because you need BOTH customer and department to match. So, you need several SUMPRODUCTS. Ugh, this is really getting difficult.

[MSP77079]

Here, for example, is the formula required for cell F9:

Please Login or Register  to view this content.

and even that is too simple, because it is using a 'fixed' reference for the Department, and we would have to complicate the formula to adjust for the fact that the department info is intermittent.

I think the only satisfactory answer is to find a way to do it in the pivot table. And I have not been able to find a way to do that in a single pivot table.

Wait ... I just had another idea to try. I haven't given up yet.

[MSP77079]

I tried using MS Query. Basically, what you would have to do is to replace the pivot table with custom controls (which would allow users to select what they now can select with the Pivot Table page and Field selections), then build the SQL for the query they want to run.

It would work, but would be an enormous VBA effort.

Now I give up.

[nandhamnk]

It is an great effort by you.Thanks

is there any simple way to solve this ?

Appreciate your suggestions

Thanks

[nandhamnk]

Can anyone suggest best way

Thanks

[VBA Noob]

Hi,

Not a answer your going to like

You could add a second table with same criteria. See attached


VBA Noob

[MSP77079]

Hi Noob, Good suggestion. Hiding the extra columns, as you have done, makes it appear as if it is a single table.

Depending on how users are going to interact with the Pivot Table ... putting the tables right next to each other might be an issue if users want to be able to move fields from Page to Field level (requiring more columns for the first Pivot Table).

To facilitate this, one could:
1. leave additional, blank, columns between the two tables
2. use Data >> Group to make hiding and showing those extra columns easier

The final problem is keeping the two tables in sync as the user makes various selections for page and field filters. This requires VBA, which I have done before (so, I should have thought to mention this!). There is a Worksheet_PivotTableUpdate event which could be used to trigger the VBA that keeps the two tables in sync.

[Carim]

Hi ...

If I may jump in ...
Do you mean your pivot table should retain the possibility of selecting Oct-Nov-Dec independently and, at the same time, have another column which groups all three months ...( i.e "All" in the month selection...) ...?

[Carim]

Hi again,

Can you change your format and have Month field appearing as a column ...?

[nandhamnk]

Thanks both VBA Noob & MSP77079

What MSP77079 saying is absolutely right.If iam going to select for Nov data or sub-select onsite/offshore problem persists.is it possible with VBA ?

Thanks

[nandhamnk]

Hi carim,

It doesn't allow me to change Month in Column in Pivot1

[Carim]

Hi,

Attached is what I meant ...
User can pick a given month in cell C8 ...

[MSP77079]

Hi all,

I was working on the VBA to align the two tables. I have not yet looked at Carim's suggestion, will do that now.

If the solution ends up with 2 pivot tables, here is the first part of the code that will be required (aligns the page fields and the current page selections):

Please Login or Register  to view this content.

I will post the workbook when I have it completely working. But, first ... to look at Carim's suggestions.

[nandhamnk]

Hi Carim,

It's Simple and rocks.The only way is to brought down the month column in data feild.I kept away the month from data column for specific reason.Is it anyway possible to keep away month from data feild.
Awaiting your reply

Thanks

[Carim]

Hi,

In this layout, the field Month is a Column ... (it is not in the Data area...)

What is your "specific reason" for not having Month as a column ...?

[nandhamnk]

Hi MSP77079,

What Carim suggesting it helps,but somer reason i kept Month in outer column.If your VBA helps to keep Month in outer column as my first post,it solves my problem like anything

Thanks for all of your efforts

[MSP77079]

I like Carim's solution as well, and am very happy to stop working on the VBA for the 2-table solution.

Just to be sure I am not missing anything ... it appears to me that if one wants to see the "YTD" numbers in the single table, they MUST display all months. So ... the single table allows the user to see the YTD total or any single month; but, not both unless they display all months. Correct?

----------------- ADDED LATER -------------
Looks like nandhamnk and I were writing at the same time. And, just for the challenge of it, I will go ahead and keep working on the 2-table solution to see if I can make a go of it.

There is one complication hidden in the Noob's work. He had to manually select which departments to show in order to get the customers to align. I fear (cannot prove) that this will be more problematic as the test data is replaced with real data. But, perhaps not. We shall see.

[nandhamnk]

Thanks for your support MSP77079

[MSP77079]

What I have so far is code to align the two pivot tables so that they have exactly the same page and field selections. The problem I have (as i expected might happen) is this ... suppose you select November ... the number of rows in the second pivot table will exceed the number of rows in the first pivot table, even if the all customers who do not show up in the first table are hidden in the second.

Why? Because for Department 3 (for example) only 2 customers had sales in November, but 19 customers have data for Department 3 for the 3 month period.

So, I am stuck at trying to figure a way around that problem.

meanwhile ... here is the code I have for keeping the two tables aligned.

Please Login or Register  to view this content.

[MSP77079]

Thinking about this some more ... if the rows are not going to line up, in general, then there is no need to have the 2 pivot tables on the same worksheet.

So, the easiest thing to do to create the all-months pivot table is to copy the table from Sheet1 to a hidden sheet. Then simply change the month page setting to "All". This greatly simplifies the code.

If you have a GETPIVOTDATA formula pointing to each of the Profit cells in Pivot Table 1 and replace the pivot table cell reference with the pivot table on the hidden sheet, you will get the Profit for all months.

The trick (which I have not yet figured out) is to set up the GETPIVOTDATA formula pointing to each of the Profit cells in Pivot Table 1. Excel does this so automatically when you simply type in "=" and click on the cell reference. But, I can't seem to figure out how to duplicate that in VBA.

[MSP77079]

Well, I was unable to find an elegant way to create the GETPIVOTDATA formulas. So, I resorted to brute force (or was it brutal force?).

The attached is written as 1 Event routine and 2 subroutines:
+ the Worksheet_PivotTableUpdate Event calls
+ copyPivotTable, which copies the pivot table to a hidden sheet and changes the month page selection to "All", then calls
+ WriteGETPIVOTDATA, which ... well ... writes the GETPIVOTDATA formulas

If anyone comes up with a less brutal way to do the latter, it should be easy to drop in the improvement.

[nandhamnk]

Thanks MSP77079.

You Made an enormorous try.

[MSP77079]

Is this NOT what you wanted? I thought it was more than a try, I thought it was a success? No?

[MSP77079]

I have found the "key" to creating the GETPIVOTDATA formula without resorting to brute force.

The example below is not based on the workbook attached above, but it illustrates the principle. When I have time, I will incorporate this into the WriteGETPIVOTDATA routine in the workbook attached above.

Please Login or Register  to view this content.

[nandhamnk]

Thank you very much

[MSP77079]

I like this one SOOoooo much more than the earlier version. For one thing, it allows the correct totals by Department and overall, not just by customer. And, because it is using built-in features of pivot tables (vs. brute force of VBA logic), I feel more confident that it will survive what users might throw at it.

I learned a lot doing this project!

[MSP77079]

One thing I did not address in the earlier version is ... what if the Profit column is moved, so that it is not the last column in the data fields?

This version solves that issue.