So I have this data that I have grouped by starting quarter for the fiscal year:
Sales Order | Account Name | Start Date | End Date | Order Type | Amount | Class | Starting Quarter |
1111 | XYZ Corp | 1/15/15 | 1/15/18 | New | 25000 | Software | Q4 FY2016 |
1111 | XYZ Corp | 1/15/15 | 1/15/18 | New | 5000 | Hardware Support | Q4 FY2016 |
1112 | The Best Company | 4/15/13 | 4/15/16 | New | 50000 | Software | Q1 FY2014 |
1113 | The Best Company | 8/16/13 | 4/15/16 | Reorder | 65000 | Software | Q1 FY2014 |
1114 | The Best Company | 4/16/16 | 4/16/19 | Renewal | 115000 | Software | Q1 FY2014 |
1114 | The Best Company | 4/16/16 | 4/16/19 | Renewal | 5000 | Hardware Support | Q1 FY2014 |
1115 | The honest company | 8/15/15 | 8/15/18 | New | 100000 | Software | Q3 FY2016 |
1116 | The honest company | 12/13/16 | 8/15/18 | Reorder | 80000 | Software | Q3 FY2016 |
I am trying to get it into this format:
Months into contract | ||||
Starting Quarter | O | 1 | 2 | 3 |
Q1 FY2014 | 50,000 | 65,000 | ||
Q2 FY2014 | ||||
Q3 FY2014 | ||||
Q4 FY2014 | ||||
Q1 FY2015 | ||||
Q2 FY2015 | ||||
Q3 FY2015 | ||||
Q4 FY2015 | 25000 | |||
Q1 FY2016 | ||||
Q2 FY2016 | ||||
Q3 FY2016 | 100000 | 80000 | ||
Q4 FY2016 | ||||
Q1 FY2017 |
How do I do it? I don't even know where to begin...
Solved! Go to Solution.
I think it would be helpful for you to define the "Months into contract" as this is a new metric that must be calculated as part of this transformation. You will most likely need to use some formulas that look at the difference between two dates using the DateTimeDiff function in Alteryx to compare the start date of a contract as it relates to the starting quarter value. Then once you make this calculation you can simply crosstab it and get the "Months into contract" as your new column headers.
Hey Brandon,
Sorry for my delayed response. While your answer didn't fully answer it it was the catalyst for me getting unstuck. I hadn't used the cross tab yet but this helped me understand how to use it. I really appreciate it!
Cheers!
Jesse