Challenge #41: Analytics PayPeriodCalc

[GeneR]

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]

Here's a solution:

Spoiler

[brianprestidge]

Better Late Then Never! 

Spoiler

My Solution:

[Joe_Mako]

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.

[SeanAdams]

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]

:-) 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]

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

Spoiler

[estherb47]

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

Spoiler

[LordNeilLord]

Solution:

Spoiler

[MsBindy]

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.