I was given an old Excel file where the user added 6 columns each day over last 3 years. So it goes way to the right. I have this data now below as you can see there are 11 categories and there are 3 locations (Site, Region and National) with each having a Total and Sales column added each day. I need to stack the data by rows so I can pivot it in Tableau. I am only sharing 3 days of reporting so you can imagine what 3 years looks like. And this is fake data....it's all fabricated, but it resembles what I have.
Date > | 8/8/2022 | 8/8/2022 | 8/8/2022 | 8/8/2022 | 8/8/2022 | 8/8/2022 | 8/7/2022 | 8/7/2022 | 8/7/2022 | 8/7/2022 | 8/7/2022 | 8/7/2022 |
Category | Site - Total | Site - Sales | Region - Total | Region - Sales | National - Total | National - Sales | Site - Total | Site - Sales | Region - Total | Region - Sales | National - Total | National - Sales |
Within 7 Days of Due | 54 | $2,104.15 | 187 | $5,557.15 | 518 | $6,844.15 | 55 | $3,892.93 | 89 | $6,025.93 | 137 | $6,615.93 |
8 to 15 Days to Due | 55 | $1,933.28 | 188 | $5,386.28 | 519 | $6,673.28 | 54 | $1,786.07 | 88 | $3,919.07 | 136 | $4,509.07 |
Up to 7 Days Past Due | 58 | $5,023.44 | 191 | $8,476.44 | 522 | $9,763.44 | 57 | $3,234.66 | 91 | $5,367.66 | 139 | $5,957.66 |
8 to 15 Days Past Due | 54 | $2,149.30 | 187 | $5,602.30 | 518 | $6,889.30 | 56 | $2,257.83 | 90 | $4,390.83 | 138 | $4,980.83 |
16 to 30 Days Past Due | 59 | $6,512.28 | 192 | $9,965.28 | 523 | $11,252.28 | 57 | $4,542.72 | 91 | $6,675.72 | 139 | $7,265.72 |
31 to 60 Days Past Due | 56 | $1,383.16 | 189 | $4,836.16 | 520 | $6,123.16 | 56 | $1,383.16 | 90 | $3,516.16 | 138 | $4,106.16 |
More Than 60 Days Past Due | 58 | $7,311.92 | 191 | $10,764.92 | 522 | $12,051.92 | 58 | $7,311.92 | 92 | $9,444.92 | 140 | $10,034.92 |
16 to 30 Days to Due | 78 | $25,223.85 | 211 | $28,676.85 | 542 | $29,963.85 | 67 | $27,102.52 | 101 | $29,235.52 | 149 | $29,825.52 |
31 to 60 Days to Due | 161 | $178,370.49 | 294 | $181,823.49 | 625 | $183,110.49 | 127 | $93,458.73 | 161 | $95,591.73 | 209 | $96,181.73 |
> 60 Days to Due | 568 | $1,189,098.92 | 701 | $1,192,551.92 | 1032 | $1,193,838.92 | 387 | $872,103.19 | 421 | $874,236.19 | 469 | $874,826.19 |
This is the results I am trying to get:
Report Date | Category | Ownership | Site - Total | Site - Sales |
8/8/2022 | Within 7 Days of Due | Site | 54 | $2,104.15 |
8/8/2022 | 8 to 15 Days to Due | Site | 55 | $1,933.28 |
8/8/2022 | Up to 7 Days Past Due | Site | 58 | $5,023.44 |
8/8/2022 | 8 to 15 Days Past Due | Site | 54 | $2,149.30 |
8/8/2022 | 16 to 30 Days Past Due | Site | 59 | $6,512.28 |
8/8/2022 | 31 to 60 Days Past Due | Site | 56 | $1,383.16 |
8/8/2022 | More Than 60 Days Past Due | Site | 58 | $7,311.92 |
8/8/2022 | 16 to 30 Days to Due | Site | 78 | $25,223.85 |
8/8/2022 | 31 to 60 Days to Due | Site | 161 | $178,370.49 |
8/8/2022 | > 60 Days to Due | Site | 568 | $1,189,098.92 |
8/8/2022 | Within 7 Days of Due | Region | 187 | $5,557.15 |
8/8/2022 | 8 to 15 Days to Due | Region | 188 | $5,386.28 |
8/8/2022 | Up to 7 Days Past Due | Region | 191 | $8,476.44 |
8/8/2022 | 8 to 15 Days Past Due | Region | 187 | $5,602.30 |
8/8/2022 | 16 to 30 Days Past Due | Region | 192 | $9,965.28 |
8/8/2022 | 31 to 60 Days Past Due | Region | 189 | $4,836.16 |
8/8/2022 | More Than 60 Days Past Due | Region | 191 | $10,764.92 |
8/8/2022 | 16 to 30 Days to Due | Region | 211 | $28,676.85 |
8/8/2022 | 31 to 60 Days to Due | Region | 294 | $181,823.49 |
8/8/2022 | > 60 Days to Due | Region | 701 | $1,192,551.92 |
8/8/2022 | Within 7 Days of Due | National | 518 | $6,844.15 |
8/8/2022 | 8 to 15 Days to Due | National | 519 | $6,673.28 |
8/8/2022 | Up to 7 Days Past Due | National | 522 | $9,763.44 |
8/8/2022 | 8 to 15 Days Past Due | National | 518 | $6,889.30 |
8/8/2022 | 16 to 30 Days Past Due | National | 523 | $11,252.28 |
8/8/2022 | 31 to 60 Days Past Due | National | 520 | $6,123.16 |
8/8/2022 | More Than 60 Days Past Due | National | 522 | $12,051.92 |
8/8/2022 | 16 to 30 Days to Due | National | 542 | $29,963.85 |
8/8/2022 | 31 to 60 Days to Due | National | 625 | $183,110.49 |
8/8/2022 | > 60 Days to Due | National | 1032 | $1,193,838.92 |
8/7/2022 | Within 7 Days of Due | Site | 55 | $3,892.93 |
8/7/2022 | 8 to 15 Days to Due | Site | 54 | $1,786.07 |
8/7/2022 | Up to 7 Days Past Due | Site | 57 | $3,234.66 |
8/7/2022 | 8 to 15 Days Past Due | Site | 56 | $2,257.83 |
8/7/2022 | 16 to 30 Days Past Due | Site | 57 | $4,542.72 |
8/7/2022 | 31 to 60 Days Past Due | Site | 56 | $1,383.16 |
8/7/2022 | More Than 60 Days Past Due | Site | 58 | $7,311.92 |
8/7/2022 | 16 to 30 Days to Due | Site | 67 | $27,102.52 |
8/7/2022 | 31 to 60 Days to Due | Site | 127 | $93,458.73 |
8/7/2022 | > 60 Days to Due | Site | 387 | $872,103.19 |
8/7/2022 | Within 7 Days of Due | Region | 89 | $6,025.93 |
8/7/2022 | 8 to 15 Days to Due | Region | 88 | $3,919.07 |
8/7/2022 | Up to 7 Days Past Due | Region | 91 | $5,367.66 |
8/7/2022 | 8 to 15 Days Past Due | Region | 90 | $4,390.83 |
8/7/2022 | 16 to 30 Days Past Due | Region | 91 | $6,675.72 |
8/7/2022 | 31 to 60 Days Past Due | Region | 90 | $3,516.16 |
8/7/2022 | More Than 60 Days Past Due | Region | 92 | $9,444.92 |
8/7/2022 | 16 to 30 Days to Due | Region | 101 | $29,235.52 |
8/7/2022 | 31 to 60 Days to Due | Region | 161 | $95,591.73 |
8/7/2022 | > 60 Days to Due | Region | 421 | $874,236.19 |
8/7/2022 | Within 7 Days of Due | National | 137 | $6,615.93 |
8/7/2022 | 8 to 15 Days to Due | National | 136 | $4,509.07 |
8/7/2022 | Up to 7 Days Past Due | National | 139 | $5,957.66 |
8/7/2022 | 8 to 15 Days Past Due | National | 138 | $4,980.83 |
8/7/2022 | 16 to 30 Days Past Due | National | 139 | $7,265.72 |
8/7/2022 | 31 to 60 Days Past Due | National | 138 | $4,106.16 |
8/7/2022 | More Than 60 Days Past Due | National | 140 | $10,034.92 |
8/7/2022 | 16 to 30 Days to Due | National | 149 | $29,825.52 |
8/7/2022 | 31 to 60 Days to Due | National | 209 | $96,181.73 |
8/7/2022 | > 60 Days to Due | National | 469 | $874.82 |
I am attaching the input data on Excel and the Workflow I have close to what I am looking for. I credit @AngelosPachis for helping earlier today to resolve the two columns. But expanding this to more columns is a challenge and I just can't see to figure it out. Do I need to add yet another set of transpose and cross tabs?
Solved! Go to Solution.
I figured it out with the help of @joelmiller66 on another posting.
User | Count |
---|---|
60 | |
24 | |
24 | |
21 | |
21 |