Need help with a calculation that separates and sums and subtracts from each other

[jthompson789]

I'm working on a workflow that has to separates a single measure into to totals then subtract one from the other. The measure has to be aggregated before it can be separated and subtract from one another. I've included a workflow that shows where I'm getting suck. Any help will be greatly appreciated.

[nabandla]

you need to use the aggregate tool, i am unable to run the flow getting this error

Could you please attach packaged workflow

BR,

NB

[viveknshah]

1) The dummy data file is missing so not sure of the format your data is in. But assuming  standard timesheet rows and columns where an individual (FTE) is adding their timesheets by the row, 

You'll need to use aggregate and group by individual depending on how are you reporting and leverage sum of whichever column you are leveraging. 

Also your logic needs to be updated to [Income Code]= "overtime" or [Income Code]="overtime2". The present condition won't work for the logic I presume you are trying to create 

[jthompson789]

Here is the packaged workflow. This problem does involve calculating FTEs, as mentioned in the other posts. 

My goal at the end of the workflow would be to have one list of position titles that have over time removed to get a net FTE number.

Thanks in advance for all your help.

[JosephSerpis · Accepted solution]

Hi @jthompson789 I have mocked up a workflow that I think produces the output you described. Let me know what you think?

[estherb47]

Hi @jthompson789 

Thanks for providing your workflow.

One easier way to do this is to separate your data into Regular time and Overtime. You can do this easily with a Filter, and the Contains (or doesn't contain) formula.

Then Summarize as needed. Wasn't sure how you wanted to roll things up, so included date, position, and hours. You can adjust as needed. Added hours per date and position. and called the new field for regular hours "Regular" and for all Overtime hours "overtime"

Joined these back together with a join, replaced nulls with zeros, and created the new subtracted field with a Formula tool.

Let me know if this helps!

Cheers,

Esther

[jthompson789]

Thank you so much for helping me with this challenge. I've spent about two days trying to figure this problem out. This workflow will help reduce a coworkers time by 3 hours every two weeks he uses to spend copying and pasting in excel to reproduce this workflow from start to finish. 

[jthompson789]

Thank you for your suggestion. I tried something like this but my logic statement was not right when I tried to rejoin the data sets and then tried to subtract the reg hours from the overtime hours.