• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get two pivot tables on one pivot chart

D

dotwil

New Member
Hi

Thank you for an excellent site. I have found many tips and guidance here.

I have a dataset that contains a column with budgets for projects and a projected invoice date. When the project is completed the invoice amount and actual date are also filled in. I've used two different pivot tables to collect the data and to group it per year and then per month. I've searched around, but it does not seem possible to plot both these datasets on one pivot table to compare projected with actual invoicing. The best advice it seems is to put all the data in one pivot table, but I stumble on that step since the database contains a date for the budget and a date for the invoice.

My workaround {=SUM((Budget)*(Budget_date<=$P4)*(Budget_date>$P3))} using dates in column P provides the desired result for budget and another for actual invoicing, but does not have the flexibility for users that data slicers that come with pivot tables offer.

Any suggestions would be appreciated.
 
I think your two options are to either combine the 2 data sets and create a single Pivot Table, or use some formulas to pull the needed info from the two Pivot Tables and create a (dynamic?) chart.
 
The thing is, the datasets are combined, but either in away that would not make it work, or I do not know how to make the pivot table.

Example:

Project. Budget. Budget_date. Invoice. Inv_date

A $10 31 Dec 11 $12 23 Jan 12

B $8 31 Jan 12 $8. 25 Jan 12

I would appreciate any suggestions on how to make a pivot table with the sum of Budget and the sum of Invoice grouped per month so that I can plot both on one pivot chart per month.

When I make the pivot table one of the two dates is the primary and then the other is the secondary (nested?), whereas all I want to do is plot budget per month vs actuals per month.

If this cannot work, please point me to some resource on how to simulate slicers so that users can customise their views easily.
 
Can you copy the Invoice columns down below and make data look like this?

[pre]
Code: 
Project	Date	        Amount	Type
A	31-Dec-11	$10.00 	Budget
B	31-Jan-12	$8.00 	Budget
A	23-Jan-12	$12.00 	Actual
B	25-Jan-12	$8.00 	Actual
[/pre]
That would make things much easier to build in the PivotTable.
 
@Luke M

Hi!

Sorry for invading this post to ask you a question.

I've just copied and pasted VBA code in another topic, and I lose the indentation after sending (but I see it indented before pressing "Send Post"!).

Is there any easy way to copy & paste? I've seen in your posts that nearly always (if not always) your code is indented. Thank you!

Regards!
 
@SirB7,


The answer for your question is posted in link below:


http://chandoo.org/wp/2011/11/04/fancy-posts-using-html-display-codes/


Regards,

Prasad DN
 
Hi, prasaddn!

Thank you very much. Just tried here and worked fine. http://chandoo.org/forums/topic/reverse-vlookup?replies=8#post-17061

Regards!
 
You must log in or register to reply here.
Back
Top