carlspywell
asked on
Pivot table from multiple sources
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
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
StephenJR
Could you combine the two datasets into one and put in the third column 1 for open and 2 for closed (say)?
ASKER
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!
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!
OK, what should the values be for Jan 2010 in the file you attached?
ASKER
Open 196 and closed 118. If you see the file i attached the values are correct on the analysis sheet.
ASKER
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
Opened-to-Closed---Copy--2-.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
You are correct @ 314 if you include all Claim Types.
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.