Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Assign % used per week derived from a date range

ISUGraber
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%

 

5 REPLIES 5
T_Willins
14 - Magnetar
14 - Magnetar

Hi @ISUGraber,

 

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

 

Iterative MacroIterative Macro

 

WorkflowWorkflow

 

mattreynolds
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!

Matt

jacob_kahn
12 - Quasar

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

 

Thanks,

Jon

T_Willins
14 - Magnetar
14 - Magnetar

Hi @ISUGraber,

 

I saved the attached file in Alteryx Designer 2018.4.

Labels