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 |
Solved! Go to Solution.
See the attached example
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