Milestone tracking data model

[jannet2014]

Hello,

I have some challenges manipulating milestones data in Alteryx and hoping someone can help.

Below is the input data that includes milestone start and end dates for 2 items. My goals is to calculate # of days it takes to complete each milestone for each item as well as to add a flag indicating whether the milestone is required or not (i.e. if there is a start date, then milestone is required, otherwise it is not). Some milestones may have a start date but no completed date so the days_to_complete will be 0, however I still want to show it as a required milestone.

I've tried to slice and dice the data different ways in Alteryx but wasn't able to get the desired output. Appreciate if anyone can point me in the right direction.

Current Input

Item #	Milestone_Category	Milestone	PerformedOn
1	Market Analysis	Market Analysis_started	1/1/20
1	Market Analysis	Market Analysis_completed	1/15/20
1	Rough Prototypes	Rough Prototypes_started	2/6/20
1	Rough Prototypes	Rough Prototypes_completed	2/26/20
1	Cost Estimates	Cost Estimates_started	1/20/20
1	Cost Estimates	Cost Estimates_completed	3/1/20
1	Architectures	Architectures Design_started	2/1/20
1	Architectures	Architectures Design_completed	2/15/20
1	Refine Design	Refine Design_started	2/23/20
1	Refine Design	Refine Design_completed
2	Market Analysis	Market Analysis_started	1/1/20
2	Market Analysis	Market Analysis_completed
2	Rough Prototypes	Rough Prototypes_started	2/6/20
2	Rough Prototypes	Rough Prototypes_completed
2	Cost Estimates	Cost Estimates_started
2	Cost Estimates	Cost Estimates_completed
2	Architectures	Architectures Design_started
2	Architectures	Architectures Design_completed
2	Refine Design	Refine Design_started
2	Refine Design	Refine Design_completed

Desired Output

Item#	Milestone_Category	#days_complete	milestone_required
1	Market Analysis	11	1
1	Rough Prototypes	15	1
1	Cost Estimates	30	1
1	Architectures Design	10	1
1	Refine Design		1
2	Market Analysis		1
2	Rough Prototypes		1
2	Cost Estimates		0
2	Architectures Design		0
2	Refine Design		0

[MichalM · Accepted solution]

@jannet2014 

• Firstly, I'd convert the dates into Alteryx friendly format (yyyy-mm-dd) using the DateTime module
• I'd then re-code the Milestone field and remove the reference to category leaving me with only Started/Completed flag
• Once the data's clean, I'd use the Crosstab module and reshape it to get Started and Completed dates on the same line
• Lastly I'd create two new variables - milestone_required and #days_complete

See the attached example

[DannyS]

hi @jannet2014 ,

I played around with this and I think the toughest part was to allocate the #days_complete column appropriately based on business days took to complete. I think @MichalM 's solution is great and simplistic if you are not worried about the business days took to complete the milestone. However, the solution I've attached allocates for business days and may be useful as well.

Hope this is helpful!

Best,

Danny