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.