community
cancel
Showing results for 
Search instead for 
Did you mean: 
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.
Getting started with Designer? | Start your journey with our new Learning Path!

Challenge #41: Analytics PayPeriodCalc

Asteroid

Here is the solution.

Asteroid

Solved. 

Solution attached.

Alteryx Certified Partner

Great challenge, I really enjoyed this one.

I think mine is different to most as I wanted to avoid using the "Generate Rows" tool as this brute force approach, while very useful in given circumstances, can massively increase the data size. So, I used something else...

 

Spoiler
I first transposed the data to get the dates in a single column, I then used the date parse tool to convert the date into a date format. With this new date, I created the "Month" column.
Now here's where it gets interesting (if you don't get out much, obviously), I split the streams, the first I cross-tabbed back to I had the P1 and P2 start and end dates in columns; the second I generated the days of the week, and filtered to only show those that were weekend days. I then applied the "Count Workdays" tool, and fed in the weekend days as the restriction list.
Simple.

Workflow.PNG
results.PNG

 

Asteroid
 
Comet
Spoiler
Challenge 41.PNGI found this article useful for getting the day of the week from the dates - https://help.alteryx.com/10.1/Reference/DateTimeFunctions.htm
Highlighted

This one took me a long time... I thought I was using way too many tools and it had to be simpler, but it ended up being very similar to the posted solution.

Asteroid

There is a great macro on the gallery to Count Weekdays (and even allows you to input holidays). Also you can use this link to check the date time formats.

Asteroid
Spoiler
Weekly Challenge - 41.PNG
Alteryx Certified Partner

Here we go!

 

Spoiler
41.png