Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #41: Analytics PayPeriodCalc

GeneR
Alteryx Alumni (Retired)

The link to last week’s challenge (challenge #40) is HERE

 

This week’s exercise looks at using Alteryx to calculate the number of weekdays during each pay period.  Employees get paid twice monthly so the number of weekend days within a period can vary.

 

Objective:  For each month and pay period, calculate the # of weekdays that make up the pay period (i.e. exclude weekend days from the calculation).

MattD
Alteryx Alumni (Retired)

Here's a solution:

Spoiler
41 solution.PNG
Former Alteryx, Inc. Support Engineer, Community Data Architect, Data Scientist then Data Engineer
brianprestidge
8 - Asteroid

Better Late Then Never! 

Spoiler
My Solution:
Screen Shot 2016-09-26 at 19.36.49.png
Joe_Mako
12 - Quasar

How about a Multi-Field Formula of:

DateTimeParse(Regex_replace('0' + Replace([_CurrentField_], '/', '/0'), '^0*(\d\d)/0*(\d\d)/0*(\d{4})', '$3-$1-$2'),'%Y-%m-%d')

to convert the strings into dates, and then a fomula step to count the weekdays like:

1+((DateTimeDiff([P1 End],[P1 Start],"days")*5 -
(ToNumber(DateTimeFormat([P1 Start],'%w'))-ToNumber(DateTimeFormat([P1 End],'%w')))*2) / 7) -
IF DateTimeFormat([P1 End],'%w')='6' THEN 1 ELSE 0 ENDIF - 
IF DateTimeFormat([P1 Start],'%w')='0' THEN 1 ELSE 0 ENDIF

Both these formulas are based on posts from @jdunkerley79 the only change I made is to use DateTimeFormat([P1 End],'%w') to get the weekday number.

 

count weekdays.png

SeanAdams
17 - Castor
17 - Castor

Hi @GeneR  / @TaraM- the startFile that's posted for this challenge (#41) appears to be the solution file for challenge 40 (it has the input data and solution canvas for #40). 

Would you mind checking if this is something on my end, or if the challenge files may have been mixed up a little?

 

Thank you

Sean

SeanAdams
17 - Castor
17 - Castor

:-) did it a long way around (see below) - but very glad to see the super-efficient method from @Joe_Mako; and the solutions from @MattD & @brianprestidge

 

Spoiler
- Appended a row to this set for every day of the month with a unique number between 1 & 31 - so each row in the original set now has 31 rows
- then converted this to a test date in the same month
- added a weekday flag to this test date
- then counted the number of rows where it was a weekday and either fell in the P1 range or the P2 range (simple summarize)

Also added a macro that makes it really easy to check for differences in column name; or value across the provided output data, and your own solution (attached)

 

NicoleJohnson
ACE Emeritus
ACE Emeritus

My solution. Had never used Generate Rows before, so had to do a little digging, but eventually figured it out.

 

Spoiler
WeeklyChallenge41.JPG
estherb47
15 - Aurora
15 - Aurora

A different approach here. Separate into 2 streams for P1 and P2. Generate individual dates and then determine weekday of each. Filter out the weekends, and sum up to Month, Year for each Pay Period

Spoiler
Spoilerimage.png
LordNeilLord
15 - Aurora

Solution:

 

Spoiler
Weekly Challenge 41.png
MsBindy
8 - Asteroid

Took me a while,  and mine looks different, but it seems to work.

 

After changing to date formats, used Min/Max to find date range for each month to deal with different days in each month.

Used Multi-Row to add a row for each day in each month

Added formulas to identify week days (0=Weekend, 1=Weekday),  then categorized the date into the P1 or P2 range

Summarized final results.