Hi -
Struggling with what I've thought would be a simple exercise. I've got a data set that will constantly be updated with new record types and options that are not necessarily known that this point. The problem is I can do the analysis now, but have to recreate / update all the reports when a new "name" or many other factors are introduced. There's no standardization for the names that come in, which is the part that I need to solve for in my update strategy. All columns are static, but the field options could change essentially. I'm now able to get all data points into a table and was hoping that I could leverage pivot tables to complete the analysis and update, but am having problems. The end goal is trying to associate how many records were modified by name, for example. I'm able to do it in pivot tables using the % to total piece, but only if both are showing. So i would have a % for modified and not modified for each name, which is not necessary especially when I need to quickly cut the data in a handful of ways and graph each. Ideally I would be able to show the % modified by name and it would include a new row each time a new name is introduced. Table below is a bare bones example of my data set with the expected output further below. Any advice / help is more than appreciated!
Expected Output for analysis / graphing (months would be across the top since the data set spans years)
Struggling with what I've thought would be a simple exercise. I've got a data set that will constantly be updated with new record types and options that are not necessarily known that this point. The problem is I can do the analysis now, but have to recreate / update all the reports when a new "name" or many other factors are introduced. There's no standardization for the names that come in, which is the part that I need to solve for in my update strategy. All columns are static, but the field options could change essentially. I'm now able to get all data points into a table and was hoping that I could leverage pivot tables to complete the analysis and update, but am having problems. The end goal is trying to associate how many records were modified by name, for example. I'm able to do it in pivot tables using the % to total piece, but only if both are showing. So i would have a % for modified and not modified for each name, which is not necessary especially when I need to quickly cut the data in a handful of ways and graph each. Ideally I would be able to show the % modified by name and it would include a new row each time a new name is introduced. Table below is a bare bones example of my data set with the expected output further below. Any advice / help is more than appreciated!
Name | Date | Modified |
A | 2/1/2020 | Yes |
A | 2/1/2020 | Yes |
B | 2/1/2020 | Yes |
B | 2/1/2020 | Yes |
B | 2/1/2020 | No |
B | 2/1/2020 | No |
B | 2/1/2020 | No |
B | 2/1/2020 | No |
B | 2/1/2020 | No |
B | 2/1/2020 | No |
C | 2/1/2020 | No |
C | 2/1/2020 | No |
Expected Output for analysis / graphing (months would be across the top since the data set spans years)
Name | Modified (%) |
A | 100% |
B | 25% |
C | 0% |