grouping pay period data

[darenson]

I have biweekly payroll data and am trying to group " Pay Periods" on a month by month basis. Normally I would extract the data by "month", however the tricky part is that the end of a Pay period for month 1 will sometimes overlap as the beginning pay period data for Month 2, Month 3, etc..

This is the Pay Period list with the Pay Period end date:

Pay Period	Pay Period End Date
PP01	1/5/2019
PP02	1/19/2019
PP03	2/2/2019
PP04	2/16/2019
PP05	3/2/2019
PP06	3/16/2019
PP07	3/30/2019
PP08	4/13/2019
PP09	4/27/2019
PP10	5/11/2019
PP11	5/25/2019
PP12	6/8/2019
PP13	6/22/2019
PP14	7/6/2019

and this is what I was aiming to group my pay periods by:

Pay Period	Pay Period End Date	Group (Month)
PP01	1/5/2019	1
PP02	1/19/2019	1
PP03	2/2/2019	1
PP03	2/2/2019	2
PP04	2/16/2019	2
PP05	3/2/2019	2
PP05	3/2/2019	3
PP06	3/16/2019	3
PP07	3/30/2019	3
PP08	4/13/2019	4
PP09	4/27/2019	4
PP10	5/11/2019	4
PP10	5/11/2019	5
PP11	5/25/2019	5
PP12	6/8/2019	5
PP12	6/8/2019	6
PP13	6/22/2019	6
PP14	7/6/2019	6

Any help with arranging this in a way that can group a PP date into one month and also group it into another month (ie. 02/02/2019 for end of Month 1 and 02/02/2019 for beginning of Month 2) would be greatly appreciated-- Thank you!

[estherb47]

Hi @darenson

Try this out. Took your initial idea of creating a field based on the month, and ran with it. First off, we want to exclude the first and last month from duplication of the first record in each month. So the Summarize determines the first and last months, which is appended to the data, and then a filter removes those. Within each of the remaining records, we want to isolate just the first, to change the grouping month to the previous grouping month. A Sample tool grabs just the first record per Group (month) field, and then a formula tool subtracts 1 from the Group (month) field (so that the record will appear with the previous month). Union the result together, and sort to return everything in order.

Almost there. Just need to take care of the months that actually have 3 periods, so there won't be an overlap. A Tile tool (unique records, based on the Group (month) field) counts how many records are within each Group, and a filter tool excludes the 4th record.

Let me know if that helps.

Cheers!

Esther

[darenson]

Hi @estherb47,

Thanks for reaching out and giving some very helpful pointers. This looks great, I just had one question with the last row (18). 

Even though PP14 is partially in July, for this case it would be the end date for all pay periods in June, because of the biweekly schedule and then 07/06 would subsequently be the beginning pay period for July as well, similar to some of the Pay Periods above. Is there anything I can do here to fix this?

Thanks again,

Danny

[estherb47 · Accepted solution]

Hi Danny,

Remove the line in the first filter, where [Group
(month)]!=[Last_Group(month)]

Then it will only remove the first row from repeating.

Cheers!
Esther
--
Esther Bezborodko
*Senior Manager*
201.650.7314 | estherbezborodko@gmail.com
beautycounter.com/estherbezborodko

*Our mission is to get safe products in the hands of everyone.*
[image: Facebook]

[darenson]

Worked like a charm, thanks!