Challenge #41: Analytics PayPeriodCalc

[millerbp]

took a bit... but we got there

Spoiler

[Nethrah24]

Done

[zachcornelison]

Submission attached

[Jonny]

useful exercise for lead-time analysis where the weekday matter, i.e. transit time etc.

Spoiler

[Ajjay]

Soln

[ncrlelia]

Challenge 41

[maharaj]

Got the ouput

[JackBurnham]

b

[romie75]

Hi! Could anyone please help me understand the logic behind each of the steps? I'm not sure it makes sense to me and even by looking at all the answers, I'm unable to replicate it on my own because I don't think I understand the logic behind the process. Thank you! 

[Elias_Nordlinder]

Here is my solution, I wrote some logic in as well in the spoiler if more people like romie75 have questions in the future 🙂 

Spoiler

1. Converting to longer String Fields with the Select button (P1 Start got wierd later on if keeping the small size of the String field).
2. Using the DateTimeParse function in the formula tool as we want to convert a string field to a date format.
-> %m/%d/%Y means that the original format is 1/1/2000 or similar, i.e month number/day number/year
3. Using Select tool again to format as Date as this is needed for the next tools.
4. Using the Generate Rows Tool as I want to see all the days that is in a specific pay period, as this is needed to calculate how many weekdays it is between.
The generate rows can create all of these days as long as you have a start and an end date which we have.

I created two flows as this was easier, one that using generate rows for P1 and one for P2.
It is really important to use the DateTimeAdd([Date,I,"Dayformat") as the Loop Expression in the end of the Generate rows tool.

5. Use the select tool again to remove all the unneccessary fields.
6. Use the Formula tool again to create the months in the output with DateTimeFormat function (As Date field to String field).
-> I use the formula DateTimeFormat([PayPeriod],"%B,%Y")
7. Also use formula tool to flag 1 if weekday and 0 if not weekday to be able to sum up later, also uses DateTimeFormat function.
8. Finally sums up all the weekdays per month and Year.
9. Join together the two flows.
10. Join with output to check if everything is correct.