Jump to content

Calculate the difference between a number and summary field


This topic is 4352 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I am creating a staffing database and need to compare the number of positions from one year to the next by category.

I have 25 buildings with two categories of employee per building and a report that sorts by building (BLD) and job category (JOB).

field.png

I have one Summary field that is the Total of FTE (full time equivalence)so if there are five people in BLD BE in JOB D7 and one works half time (.5 FTE) the field (SUMFTE) summarizes to 4.5

That works fine to calculate and report the current number of staff by building (BLD) and job category (JOB).

The problem is I need to calculate the difference between last years and this years staff by building (BLD) and job category (JOB). There was no database for previous years - staffing was summarized on a Word document - so I have numbers to compare and have added a table with four fields, BLD (each of 25 buildings), JOB (the two categories of job), NUM (the number of staff in that job / building), and Count (a Summary field that provides Total of NUM by BLD and JOB and is, of course, equal to NUM) to my database.

The question is how, in a report, to calculate NUM - SUMFTE or Count - SUMFTE

Link to comment
Share on other sites

Assuming you have a relationship between the two tables, based on matching Building AND Job, your calculation should be something like:

Sum ( History::NUM ) - GetSummary ( sTotalFTE ; Job )

This will work on the Job level.

Link to comment
Share on other sites

Assuming you have a relationship between the two tables, based on matching Building AND Job, your calculation should be something like:

Sum ( History::NUM ) - GetSummary ( sTotalFTE ; Job )

This will work on the Job level.

Something does not work - I tried as written ad the result was equal to ( History::NUM ). I then tried a field named Fred that was a calculation: GetSummary ( sTotalFTE ; Job ). The field did not display in the report although it was in the layout.

It seems that GetSummary ( sTotalFTE ; Job ) is ignored or voided out.

This is probably a stupid question to raise.....in your example you reference the field with a lower case /s/ preceding the field name. Is this simply to denote a Summary field in your explanation? When I used GetSummary in my calculation I just used the Summary field name, e.g. GetSummary ( Count Positions by FTE; JOB )

Thanks

Link to comment
Share on other sites

Assuming you have a relationship between the two tables, based on matching Building AND Job, your calculation should be something like:

Sum ( History::NUM ) - GetSummary ( sTotalFTE ; Job )

This will work on the Job level.

Thanks - this works.....I was sorting the report on a field that calculated JOB (which is an AlphaNumeric reference) into a name, e.g. Teacher, Instructional Assistant.

The break field in GetSummary needs to be the field the sort is based on - once I changed it the calculation works.

Thanks!

Link to comment
Share on other sites

This topic is 4352 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.