Challenge #41: Analytics PayPeriodCalc

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).

challenge_41_solution.yxmd

challenge_41_start_file.yxmd

Parse

Preparation

Advanced

Data Preparation

Transform

Basic

Accepted answers

All comments

MattD

Here's a solution:

Spoiler

brianprestidge

Better Late Then Never!

Spoiler

My Solution:
Screen Shot 2016-09-26 at 19.36.49.png

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.

count weekdays.png

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)

challenge_41_SeanSolution.yxzp

NicoleJohnson

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

Spoiler

challenge_41_NicoleJohnson.yxmd

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

challenge_41_EHB_solution.yxmd

LordNeilLord

Solution:

Spoiler

challenge_41_LNL.yxmd

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.

challenge_41_MsBindy.yxmd

jamielaird

FYI @JoeM - the start file is missing the bundled data and (very generously) includes a solution.

I created a new start file using the most recent posted solution so you can switch out the files.

challenge_41_start_file.yxzp

jamielaird

Here's my solution. Very pleased with the Generate Rows tool that increments by day between the Start and End dates - incredibly satisfying. I can see that others managed to use fewer tools but this did the job nicely for me.

Spoiler

challenge_41_JL.yxzp

nick_ceneviva

Solution Attached

challenge_41_Ceneviva.yxmd

patrick_digan

Spoiler

challenge_41_PD.yxzp

marcreid

Solution

Spoiler

challenge_41_MR.yxmd

Natasha

I remember the joy I had a while ago when I finally figured out how to make 'Generate row' tool to work with dates.

Spoiler

challenge_41_Natasha.yxmd

JoshKushner

For 'beginner' level difficulty this was particularly tricky. Quite enjoyed the double cross tab.

challenge_41_start_file.yxmd

samN

Pretty round about method but got there in the end.

challenge_41.yxmd

ggruccio

Lots of steps, but got the results.

challenge_41_gg_finish.yxmd

philipmannering

Solution attached.

Spoiler

challenge_41 - Date formatting for number of paydays.yxmd

LandonG

Solution attached.

challenge_41_start_file.yxmd

jasperlch

Solution attached.

challenge_41_JL.yxmd

msicak

tested out some tools I have not used much before. A fun exercise!

Spoiler

challenge41_MS.yxmd

BrendaS

This frustrated me to no end! In that this is a function one can do in excel with one formula, but takes 10+ steps here! But I got it done!

challenge_41_Brendas solution.yxmd

derekbelyea

Spoiler

challenge_41_JDB_AnalytixStudio.yxmd

dsmdavid

Long detour... Would have been useful to know that generate rows can work with dates as well....

Spoiler

challenge_41_solved.yxmd

JosephSerpis

Challenge Completed

Challenge_41_Joe_Serpis.yxmd

CHarrison

Found this one rather fiddly!

Challenge 41.yxmd

Elena_Caric

Nice challenge

challenge_41_start_file.yxmd

Ozzy_Campos

My solution, used 13 tools but solved it in a pretty straightforward manner.

Challenge_41_Ozzy_Campos.yxmd

JamesLawlor

Spoiler Attacehd

challenge_41_JCL.yxmd

luciebrett

Oups, completely different solution to the suggestion and also the order of my months is by date rather than alphabetical (but this makes more sense to me so I have left like that!)

challenge_41_LB.yxmd

mjholland

Looks like there are loads of different ways around this one. I tried to force myself to use as few as possible so ended up going back over work I'd already done to do more refinements …