Assign % used per week derived from a date range

[ISUGraber]

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%

[T_Willins]

Hi @ISUGraber,

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

Iterative Macro

Workflow

[mattreynolds]

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!

Matt

[jacob_kahn]

@T_Willins 

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

Thanks for sharing with us on the community.

Sincerely,

[ISUGraber]

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

Thanks,

Jon

[T_Willins · Accepted solution]

Hi @ISUGraber,

I saved the attached file in Alteryx Designer 2018.4.