Multiple Row Formula Question

[canddig]

Hello All, 

I am trying to write a formula that takes an employees name and end date for a pay period then adds all the hours worked for that pay period and employee. The pay codes can all be different though like sometimes there is overtime hour and sometimes there is bereavement. So it needs to add together all the different hours that employee worked in that pay. 

I then have to take that number and divide it by 80 (if they are a full time employee) to determine if they are a 1.0 or below or  above based on the overtime they might have worked. 

Finally I have to look at the hours worked vs the hours earned. Employees earn PTO each pay but this is not paid out until they use their PTO so the Regular Hours they work only need to be taken into consideration for the FTE portion but they need to show that they earned Regular Hours + PTO. 

I know this is a doozy. I have been trying for a week to work it out with no luck so I am reaching out to the pros. Is this even possible?  

Some sample data and normal way I would do the FTE calc is attached

I need to look at their employment category and identify how many hours they worked in that pay period then see if that

[gabrielvilella · Accepted solution]

Everything that has a defined logic to it, you can create a workflow for that. I encourage you to start with the summarize, filter and formula tools. You just need to keep in mind that, in Alteryx, it is better to work with your data in rows. Here is an example. 

[canddig]

@gabrielvilella This is a great reminder and I was able to think through half of my problem using the summarize tool. Thanks so much. :)