Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

grouping pay period data

darenson
7 - Meteor

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 PeriodPay Period End Date
PP011/5/2019
PP021/19/2019
PP032/2/2019
PP042/16/2019
PP053/2/2019
PP063/16/2019
PP073/30/2019
PP084/13/2019
PP094/27/2019
PP105/11/2019
PP115/25/2019
PP126/8/2019
PP136/22/2019
PP147/6/2019

 

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

Pay PeriodPay Period End DateGroup (Month) 
PP011/5/20191
PP021/19/20191
PP032/2/20191
PP032/2/20192
PP042/16/20192
PP053/2/20192
PP053/2/20193
PP063/16/20193
PP073/30/20193
PP084/13/20194
PP094/27/20194
PP105/11/20194
PP105/11/20195
PP115/25/20195
PP126/8/20195
PP126/8/20196
PP136/22/20196
PP147/6/20196

 

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!

4 REPLIES 4
estherb47
15 - Aurora
15 - Aurora

Hi @darenson

image.png

 

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
7 - Meteor

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

 

clipboard_image_1.png

 

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
15 - Aurora
15 - Aurora
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
7 - Meteor

Worked like a charm, thanks!

Labels