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

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.

Dummy Hours workflow.yxmd

Workflow

Accepted answers

JosephSerpis

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

Dummy Hours workflow.yxzp

All comments

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,

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.

Dummy Hours workflow.yxzp

JosephSerpis

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

Dummy Hours workflow.yxzp

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

Dummy Hours workflow.yxzp

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.

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703