Someone is looking to have their reports sorted by pay period, is this possible?

[rmartori]

So I was given an assignment to help with a workflow that was partially made. Basically they have a report that they pull usually once a week/every other week that lists who was working on what project based on the charge code used.

They wanted to segment the data by week or by pay period, we get paid every two weeks and it runs wed to wed so for So using the example below a pay period would be July 1st - 15th.

The part that is already done on the work flow involves the fiscal year/period. Because there are multiple fiscal periods in the report there are duplicates of the employees so they basically took  each fiscal period and made it it's own column instead of one column.

So we are wondering if it is possibly to segment data based on a week or pay period.

Sample of original data data(in the actual data Charge code is merged with the blank cell.)

Employee name	Fiscal year/period	Charge code		Document Date	Hours	Cost
Jane Doe	001/2021	10100101101	Daves auto rental	07/01/2020	1
John Smith	002/2021	10100101101	Daves auto rental	07/08/2020	1
Bob Bobson	001/2021	10100101101	Daves auto rental	07/10/2020	1
Hope Surname	001/2021	10100101101	Daves auto rental	07/17/2020	1
Inglebert Humperdink	001/2021	10100101101	Daves auto rental	07/23/2020	1
Jane Doe	001/2021	10100101101	Daves auto rental	07/20/2020	1

Data after being run through the bottom part of the workflow It puts each of the fiscal periods in it's own column and puts the total hours for each.

Task group	Name	001_2021	002_2021
Daves auto rental	Jane Doe	164	52
Daves auto rental	John Smith	76	25
Daves auto rental	Bob Bobson	43	18
Daves auto rental	Hope Surname
Daves auto rental	Inglebert Humperdink	2.5

Screenshot of workflow

Basically they would like to further break this up by Pay period or by week if that is possible. Which is what the top fork of the workflow is meant to be.

[KrishnaKuchibhotla]

Hi @rmartori,

If I correctly understand you are looking to have separate columns for each period cycle and then aggregate the hours that employer needs to pay to employee.

Attached the alteryx workflow. Hope this satisfies your requirement

[rmartori]

More like They want to combine all the duplicate names but break their hours up by Fiscal period and also either week or pay week. We already have the fiscal year part.

So to change the example a bit.

Original:

Employee name	Fiscal year/period	Charge code	Company name	Document Date	Hours	Cost
Jane Doe	001/2021	10100101101	Daves auto rental	07/01/2020	1
Jane Doe	001/2021	10100101101	Daves auto rental	07/08/2020	1
Jane Doe	001/2021	10100101101	Daves auto rental	07/15/2020	1
Jane Doe	002/2021	10100101101	Daves auto rental	08/26/2020	1
Jane Doe	002/2021	10100101101	Daves auto rental	08/19/2020	1
Jane Doe	002/2021	10100101101	Daves auto rental	08/20/2020	1
Jane Doe	001/2021	10100101101	Daves auto rental	07/22/2020	1
Jane Doe	001/2021	10100101101	Daves auto rental	07/29/2020	1
Jane Doe	002/2021	10100101101	Daves auto rental	08/05/2020	1
Jane Doe	002/2021	10100101101	Daves auto rental	08/12/2020	1

to something like this. Are pay periods are always on the 15th, and then the end of the month(30th or 31st depending)

Employee name	Charge code	Company Name	001/2021	002/2021	7/1-7/15	7/16-7/31	8/1-8/15	8/16-8/31
Jane Doe	10100101101	Dave's auto rental	5	5	3	2	2	3

[KrishnaKuchibhotla · Accepted solution]

Sure. Can you please download the above alteryx file and change the cross tab tool group selection similar to below?.

Note - The current logic caters for the Pay group data segment. I hope you this can be amended to your logic. Feel free to message me for any help.

If this solves your problem,please mark this as a accepted solution.

Regards

Krishna Kuchibhotla

[rmartori]

There is one last thing I would like to do If possible. To have the Fiscal year displayed more as such.

 

Right now the whole thing outputs as.

Basically it puts everything into pay period 1 or 2 ignoring the month entirely, in this example there is july and august but they all end up in columns F & G.

So in cases where there are multiple months in the report we'd like it to extend into H, I, and so on. Or if possible have it take the month into account some how.

Everything before that is displaying perfectly