Dear Alteryx community,
To create an output just like Pivot table in Excel (with 2 fields on Column), I have search many posts about Crosstab and Transpose to try out but haven't succeeded yet.
Crosstab tool is great but it doesn't support more than 1 column header.
Input:
| Location | Year | Quarter | New | Close | Sum |
| A | 2019 | Q1 | 1 | -1 | 0 |
| A | 2019 | Q2 | 1 | -1 | 0 |
| A | 2019 | Q3 | 1 | -1 | 0 |
| A | 2019 | Q4 | 1 | -1 | 0 |
| B | 2019 | Q1 | 10 | -1 | 9 |
| B | 2019 | Q2 | 10 | -1 | 9 |
| B | 2019 | Q3 | 10 | -1 | 9 |
| B | 2019 | Q4 | 10 | -1 | 9 |
| A | 2020 | Q1 | 2 | -1 | 1 |
| A | 2020 | Q2 | 2 | -1 | 1 |
| A | 2020 | Q3 | 2 | -1 | 1 |
| A | 2020 | Q4 | 2 | -1 | 1 |
| B | 2020 | Q1 | 20 | -1 | 19 |
| B | 2020 | Q2 | 20 | -1 | 19 |
| B | 2020 | Q3 | 20 | -1 | 19 |
| B | 2020 | Q4 | 20 | -1 | 19 |
Expected Output: (2 different tables can be saved in one Sheet).
I will use these table to create charts in Excel for each location, so this format need to be kept this way.
| | 2019 | 2019 | 2019 | 2019 | 2020 | 2020 | 2020 | 2020 |
| A | Q1 | Q2 | Q3 | Q4 | Q1 | Q2 | Q3 | Q4 |
| New | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 |
| Close | -1 | -1 | -1 | -1 | -1 | -1 | -1 | -1 |
| Sum | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 |
| | 2019 | 2019 | 2019 | 2019 | 2020 | 2020 | 2020 | 2020 |
| B | Q1 | Q2 | Q3 | Q4 | Q1 | Q2 | Q3 | Q4 |
| New | 10 | 10 | 10 | 10 | 20 | 20 | 20 | 20 |
| Close | -1 | -1 | -1 | -1 | -1 | -1 | -1 | -1 |
| Sum | 9 | 9 | 9 | 9 | 19 | 19 | 19 | 19 |
I appreciate every suggestion or work-around solution for this to work.
Thank you very much and Best Regards,
Duong
