Challenge #41: Analytics PayPeriodCalc

[NicoleDuhamel]

solution attached

[NicoleDuhamel]

@romie75 

Here's the workflow that I used - logic is like this:

- Record ID first so you've got a unique identifier for each row,

- Transpose to bring all of the date columns into one column & DateTime to convert that column to date format. This means that you don't have to convert each of the initial columns separately.

Text to Columns splits out the period ref from the start/end, so you can use the CrossTab to get the Start and End dates in different columns.

At this point you now have a Name column (which identifies if it's P1 or P2), an End date column and a Start date column. With the data in this layout you can use Generate Rows to add rows for each individual day. You can then use a formula to format each date with the day name and the month name , so you can remove the weekend days.

Once you have the day names and month names in separate columns, add a filter to remove Saturdays and Sundays and then use a Summarize tool to count the number of weekdays for each group & month. Then a CrossTab to show the P1 and P2 groups in their own columns.

[jcollake]

Spoiler

Solved.

[dfurlow]

This was useful for dateTimeFormat practice, especially.  Don't think i will remember the formats like %B, %a, %Y, so it's good to have a workout.

[ExploreMore]

This one took me for a little bit of a ride! I almost solved it without having to run the workflow to check my work in the middle of building it!

Spoiler

 

The link below was definitely helpful. 

DateTime Functions | Alteryx Help

[BenFr]

Quite proud of this one...even if it took me far too long!

Spoiler

Logic for formulas to calculate the weekend days:

//Sum up duration of pay period and the day that the pay period started on (have to change a Sunday 0 code to 7)

//Divide this number by 7, floor to round down and times this by 2 to get the total possible weekend days in the starting week and pay period

2*Floor((IIF(ToNumber(DateTimeFormat([P1 Start_Out],'%w'))==0,7,ToNumber(DateTimeFormat([P1 Start_Out],'%w')))+[P1 Dur])/7)+

//Take away one if ends on a Sat
IIF(ToNumber(DateTimeFormat([P1 End_Out],'%w'))==6,-1,0)+

//Take away one if starts on a Sunday
IIF(ToNumber(DateTimeFormat([P1 Start_Out],'%w'))==0,-1,0)

[VizChic]

FG soln AA 41

[carmenrapariz]

Spoiler

[kcfuiks]

Messy but complete. 😅

[RashedDS]

My solution

Spoiler