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
SOLVED

Assign % used per week derived from a date range

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

 

Highlighted
13 - Pulsar

Hi @ISUGraber,

 

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

 

Iterative MacroIterative Macro

 

WorkflowWorkflow

 

Highlighted
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

Highlighted
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,

Highlighted
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

Highlighted
13 - Pulsar

Hi @ISUGraber,

 

I saved the attached file in Alteryx Designer 2018.4.

Labels