I have a large data set and I am hoping to create a pivot table off of it.
My concern is that I don't think it is possible to do how I am envisioning it. I keep getting a big red "X" and denied sound in my head when I think about how to create this pivot table.
My data table has multiple "Milestone" columns. The milestones are: Sent, Received, Scheduled, Worked, & Completed.
For each of those milestone columns, I have a helper column to indicate where a particular record is at within the different milestones.
For example, Past Due >90, Past Due 61-90, Past Due 31-60, Past Due 1-30, Due in 0-30, Due in 31-60, Due in 61-90, Due in >90. Let's call these the "Time Frame Categories".
So a single record could have one of any of those Time Frame Categories for any of the various Milestones.
I also have each record categorized as Easy or Hard. This is the Type Category.
I need to show the Milestones as columns in my pivot table, and the Type Categories as rows, with the Time Frame Categories as rows too, under the Type Categories.
So a record will be counted 5 times, once for each milestone.
I need it as a pivot table so that I can drill down to the individual levels of the various intersects between the rows and columns.
The image is an example of what I am looking for.
The issue I foresee is that the Time Frame category is not applicable to the record as a whole, but applicable to the milestone of the record, with each record having multiple milestones, and each milestone having a different possible Time Frame than other records for the same milestone. This causes the record to be counted 5 times, whereas a pivot table, to my understanding, only provides data on a record once, based upon the various row, column, and filter selections of the pivot table fields.
I honestly don't think that it is possible, unless I change the layout of my source data for the pivot table, but I really need to keep it the same. I need to be able to drill down based upon a selection from the pivot table and present the data in the order and fashion needed.
I have done many pivot tables in the past, but something of this type has always stumped me.
Any thoughts, ideas, suggestions, recommendations, would be very helpful!!
Thank you!!
-Spydey
My concern is that I don't think it is possible to do how I am envisioning it. I keep getting a big red "X" and denied sound in my head when I think about how to create this pivot table.
My data table has multiple "Milestone" columns. The milestones are: Sent, Received, Scheduled, Worked, & Completed.
For each of those milestone columns, I have a helper column to indicate where a particular record is at within the different milestones.
For example, Past Due >90, Past Due 61-90, Past Due 31-60, Past Due 1-30, Due in 0-30, Due in 31-60, Due in 61-90, Due in >90. Let's call these the "Time Frame Categories".
So a single record could have one of any of those Time Frame Categories for any of the various Milestones.
I also have each record categorized as Easy or Hard. This is the Type Category.
I need to show the Milestones as columns in my pivot table, and the Type Categories as rows, with the Time Frame Categories as rows too, under the Type Categories.
So a record will be counted 5 times, once for each milestone.
I need it as a pivot table so that I can drill down to the individual levels of the various intersects between the rows and columns.
The image is an example of what I am looking for.
The issue I foresee is that the Time Frame category is not applicable to the record as a whole, but applicable to the milestone of the record, with each record having multiple milestones, and each milestone having a different possible Time Frame than other records for the same milestone. This causes the record to be counted 5 times, whereas a pivot table, to my understanding, only provides data on a record once, based upon the various row, column, and filter selections of the pivot table fields.
I honestly don't think that it is possible, unless I change the layout of my source data for the pivot table, but I really need to keep it the same. I need to be able to drill down based upon a selection from the pivot table and present the data in the order and fashion needed.
I have done many pivot tables in the past, but something of this type has always stumped me.
Any thoughts, ideas, suggestions, recommendations, would be very helpful!!
Thank you!!
-Spydey
