Cohort: Pivot Table to show the first few months of a new transaction

[jmorris24]

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...

[BrandonB · Accepted 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. 

 

 

[jmorris24]

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