Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Assign % used per week derived from a date range

8 - Asteroid

Hi everyone, I was hoping someone could help me with this.

I have a table of data where I have when a truck arrives in a location and when it departs.  I want to calculate the % of the week the truck was in that location.  


My data looks like this:
Location          Truck     Arrival Date     Depart Date

Pizza Parlor        A           5/8/2020           5/13/2020

Burger Place       B           5/5/2020           5/6/2020

Pizza Parlor         B           5/6/2020           5/13/2020

Burger Place        A            5/5/2020          5/8/2020

Taco Place           B            5/1/2020           5/6/2020

Taco Place          A              5/1/2020           5/5/2020


I would like to arrange these by Week Ending Dates of Sunday.  

So for Week Ending May 10th (Sunday) It would be something like (My calculations may be off):

                                   Taco          Burger          Pizza
May 10th  Truck A       57%          28%             14%

May 10th  Truck B       74%          0                   26%


13 - Pulsar

Hi @ISUGraber,


I was able to use an iterative macro to get the result you are looking for.


Iterative MacroIterative Macro




9 - Comet

Took this approach with Generate Rows. Just needs a little tweaking to address when a departure date and an arrival date coincide (wasn't sure from the post how to break the tie here, so noted in the workflow). 


Hope this helps!


12 - Quasar



This is amazing! I always found iterative macros hard to understand - but this is amazing!


Thanks for sharing with us on the community.



8 - Asteroid

Thanks @T_Willins  but I can't view your macro wf since it's in a newer version of Alteryx than what i have.


I'll look through your screenshots




13 - Pulsar

Hi @ISUGraber,


I saved the attached file in Alteryx Designer 2018.4.