Would love some help working with an input file that has aggregate counts instead of actual records.
My input file contains fill rates for codes 0-10 and I need 2 matrixes depicting totals. The matrixes cannot be drilled down as this is a static presentation.
Note: Code 0 means bad data input, so although we attribute for it in Matrix 1 (total fill rates) we don't use it for Matrix 2 (absence_presence).
Matrix 1: Full File Count by Code:
Column A = Codes 0-10
Column B = Total Input by Code
Column C = % Total Input by Code
Column D = Cumulative % desc order (Code 10 to 0)
Desired outcome:
| Full File Count by Code | | |
| Code | Full File Count | % | Cumulative % |
| 10 | 0 | 0% | 0% |
| 9 | 13 | 16% | 16% |
| 8 | 2 | 2% | 18% |
| 7 | 8 | 10% | 28% |
| 6 | 5 | 6% | 34% |
| 5 | 17 | 21% | 55% |
| 4 | 9 | 11% | 66% |
| 3 | 0 | 0% | 66% |
| 2 | 0 | 0% | 66% |
| 1 | 0 | 0% | 66% |
| 0 | 28 | 34% | 100% |
Matrix 2: Absence_Presence of each attribute for Codes 1-10. The denominator for Presence and Absence is Full File Input total for 1-10 (don't use Code 0 in these totals).
Column A = Attribute
Column B = Presence Count
Column C = Presence %
Column D = Absence Count
Column E = Absence %
Desired outcome:
| Absence_Presence for Code 1-10 | | | |
| Attribute | Present | Present % | Absent | Absent % |
| Full File Input | 54 | 100% | 0 | 0% |
| Present Customer Name | 53 | 98% | 1 | 2% |
| Present Customer Street | 51 | 94% | 3 | 6% |
| Present Customer City | 52 | 96% | 2 | 4% |
| Present Customer State | 33 | 61% | 21 | 39% |
| Present Customer Zip | 51 | 94% | 3 | 6% |
| Present Customer Country | 54 | 100% | 0 | 0% |
| Present Customer Phone | 37 | 69% | 17 | 31% |
As a bonus, I would like Matrix 2 to sort in the order shown. Is creating an Attribute table with a column for sort order the best way to do this?
The Excel file with data is here: https://www.dropbox.com/s/s4r4tjldeujk38e/master_data_sample.xlsx?dl=0
Thank you for any direction!
