Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pivot table from multiple sources

Posted on 2011-02-18
9
Medium Priority
?
458 Views
Last Modified: 2012-06-27
This is a hard one to explain but here goes:

I have external data that basically shows a case number, an open date and a closed date.
I want to create a pivot table to include the sum of the open and the sum of the closed on a month by month basis.
Sounds simple but if i create a single data connection to the data and then create the pivot table to show open cases and closed cases inthe values and the days and months in the row labels it will give me the sum of the open cases in a particular month correctly but the closed, instead of being the sum of the cases closed in the month shows the sum of the closed based on those opened in that month. (if that makes sense!)

My initial thought was to create two data connections, one to pull in the open cases count and one to pull in the closed count, then combine them into a single pivot table. Is this possible?

I have included a copy of my work so far. As you can see there are two tabs that are pulled from the data source "Opened" & "Closed" and an Analysis tab where the pivot table is.

On the analtsis i have created two pivot tables, one for the open and one for the closed. Also shown is a set of data that link to both tables and shows Opened, Closed and the Difference. This is the info i need in the single pivot table.

Is this possible?

Thanks in advance.


Opened-to-Closed---Copy.xlsx
0
Comment
Question by:carlspywell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 34927038
Could you combine the two datasets into one and put in the third column 1 for open and 2 for closed (say)?
0
 

Author Comment

by:carlspywell
ID: 34927327
I had a single source origionally with open & closed dates then i created a column for open with 1 in and a further column for closed with a one in (so it would use the sum of these to show the number open and the number closed) however, The data does not seem to match up!

Here is the closed data for 2010 as it should be

      Jan      118
      Feb      143
      Mar      151
      Apr      119
      May      127
      Jun      83
      Jul      116
      Aug      113
      Sep      162
      Oct      150
      Nov      106
      Dec      91

And here is 2010 when using the combined source: (the 2nd column is the opened)

Jan      196      67
Feb      236      111
Mar      329      116
Apr      272      64
May      191      44
Jun      238      55
Jul      213      66
Aug      142      33
Sep      231      42
Oct      172      26
Nov      195      16
Dec      205      12

As you can see the data is not the same. I don't know why!
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 34927436
OK, what should the values be for Jan 2010 in the file you attached?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:carlspywell
ID: 34927539
Open 196 and closed 118. If you see the file i attached the values are correct on the analysis sheet.
0
 

Author Comment

by:carlspywell
ID: 34927581
I have attached a further file where sheet 1 shows the result of the combined data from the DATA Sheet
Opened-to-Closed---Copy--2-.xlsx
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 34927656
Might be a red herring, but I make it 314 closed. I get the same values in the attached which is a single dataset to which I've added a year and month column (if you group  by month I don't think a PT allows for the year).
Book2.xls
0
 

Author Comment

by:carlspywell
ID: 34927697
Apologies, I forgot to mention that they are filtered by Claim Type to Include only "R" & "M".

You are correct @ 314 if you include all Claim Types.

0
 
LVL 24

Expert Comment

by:Tracy
ID: 35225335
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question