#Hi. I have been testing all kinds ways of using transpose and cross tabs and I am not able to get exactly the view I need to write data to xydb file.
I have three years of data in an Excel file that summarizes two values each date and it appends to the right of 11 categories. This is how sample of the data looks in Excel:
Date > | 8/8/2022 | 8/8/2022 | 8/7/2022 | 8/7/2022 | 8/4/2022 | 8/4/2022 |
Category | Site - Total | Site - Sales | Site - Total | Site - Sales | Site - Total | Site - Sales |
Within 7 Days of Due | 54 | $2,104.15 | 55 | $3,892.93 | 57 | $3,292.99 |
8 to 15 Days to Due | 55 | $1,933.28 | 54 | $1,786.07 | 54 | $2,104.15 |
Up to 7 Days Past Due | 58 | $5,023.44 | 57 | $3,234.66 | 54 | $1,783.45 |
8 to 15 Days Past Due | 54 | $2,149.30 | 56 | $2,257.83 | 58 | $6,926.74 |
16 to 30 Days Past Due | 59 | $6,512.28 | 57 | $4,542.72 | 58 | $5,593.95 |
31 to 60 Days Past Due | 56 | $1,383.16 | 56 | $1,383.16 | 57 | $1,436.25 |
More Than 60 Days Past Due | 58 | $7,311.92 | 58 | $7,311.92 | 58 | $7,072.57 |
16 to 30 Days to Due | 78 | $25,223.85 | 67 | $27,102.52 | 73 | $34,765.12 |
31 to 60 Days to Due | 161 | $178,370.49 | 127 | $93,458.73 | 132 | $185,245.45 |
> 60 Days to Due | 568 | $1,189,098.92 | 387 | $872,103.19 | 557 | $1,435,681.07 |
No Category Data | 176 | $53.00 | 190 | $53.00 | 119 | $53.00 |
This is how I need it to look so I can pivot it in Tableau for visualizations:
Date | Category | Site - Total | Site - Sales |
8/8/2022 | Within 7 Days of Due | 54 | $2,104.15 |
8/8/2022 | 8 to 15 Days to Due | 55 | $1,933.28 |
8/8/2022 | Up to 7 Days Past Due | 58 | $5,023.44 |
8/8/2022 | 8 to 15 Days Past Due | 54 | $2,149.30 |
8/8/2022 | 16 to 30 Days Past Due | 59 | $6,512.28 |
8/8/2022 | 31 to 60 Days Past Due | 56 | $1,383.16 |
8/8/2022 | More Than 60 Days Past Due | 58 | $7,311.92 |
8/8/2022 | 16 to 30 Days to Due | 78 | $25,223.85 |
8/8/2022 | 31 to 60 Days to Due | 161 | $178,370.49 |
8/8/2022 | > 60 Days to Due | 568 | $1,189,098.92 |
8/8/2022 | No Category Data | 176 | $53.00 |
8/7/2022 | Within 7 Days of Due | 55 | $3,892.93 |
8/7/2022 | 8 to 15 Days to Due | 54 | $1,786.07 |
8/7/2022 | Up to 7 Days Past Due | 57 | $3,234.66 |
8/7/2022 | 8 to 15 Days Past Due | 56 | $2,257.83 |
8/7/2022 | 16 to 30 Days Past Due | 57 | $4,542.72 |
8/7/2022 | 31 to 60 Days Past Due | 56 | $1,383.16 |
8/7/2022 | More Than 60 Days Past Due | 58 | $7,311.92 |
8/7/2022 | 16 to 30 Days to Due | 67 | $27,102.52 |
8/7/2022 | 31 to 60 Days to Due | 127 | $93,458.73 |
8/7/2022 | > 60 Days to Due | 387 | $872,103.19 |
8/7/2022 | No Category Data | 190 | $53.00 |
8/4/2022 | Within 7 Days of Due | 57 | $3,292.99 |
8/4/2022 | 8 to 15 Days to Due | 54 | $2,104.15 |
8/4/2022 | Up to 7 Days Past Due | 54 | $1,783.45 |
8/4/2022 | 8 to 15 Days Past Due | 58 | $6,926.74 |
8/4/2022 | 16 to 30 Days Past Due | 58 | $5,593.95 |
8/4/2022 | 31 to 60 Days Past Due | 57 | $1,436.25 |
8/4/2022 | More Than 60 Days Past Due | 58 | $7,072.57 |
8/4/2022 | 16 to 30 Days to Due | 73 | $34,765.12 |
8/4/2022 | 31 to 60 Days to Due | 132 | $185,245.45 |
8/4/2022 | > 60 Days to Due | 557 | $1,435,681.07 |
8/4/2022 | No Category Data | 119 | $53.00 |
but the closest I can get is this:
I have to figure out how to get the columns correct as well as resolve the dates adding the "_2" and then moved over to first column.
Any advice will be greatly appreciated. Thanks!
Solved! Go to Solution.
Thank you, @joelmiller66 ! This got me real close and I was able to get it to work by adding one formula tool to create "ownership" and "value3" column and I update the following Cross Tab tool with the new column. See attached final version! So grealy appreciated for the nudge in the right direction.