4 Replies Latest reply on Sep 4, 2018 11:06 AM by Deepak Rai

# Calculating the sum of a specific field only when certain fields are unique

I have a dataset of public safety call for service records. I am trying to calculate the total number of minutes staff spent with individuals who have frequent contact with law enforcement. I'd like to get the sum of minutes spent for each unique call for service (Dis ID) and each officer on that call (Call For Service Officer Id). For example, if Officer Jones spends 10 minutes on a particular call and Officer Smith spends 15 minutes on the same call, I would like a total of 25 minutes for that call. This works well when a call has no updates, such as the first two columns here.

I thought this would be straight forward, but our dispatch system creates a new record each time a call is updated and for each staff member assigned to a call. For example, a call for service with 2 staff members and 2 updates by dispatch creates a total of 6 records in the database. That would triple our time spent to 75 minutes. You can see this in the two columns on the right above.

How can I get a total only when there is a distinct "Dis ID" and "Call For Service Officer Id" combination? This would give me the the correct number of minutes, regardless of how many records there are for a particular officer on a particular call. Or is there a better solution?

Dave

• ###### 1. Re: Calculating the sum of a specific field only when certain fields are unique

Hi David,

I see Distinct DisID , Call for Service Offer ID

From this below screen could you plz let me know which rows you want to show

BR,

NB

• ###### 2. Re: Calculating the sum of a specific field only when certain fields are unique

if I understand the dispatch system is duplicating the record at the officer / dis id level

see the attached to see if it resolves the problem

it will return this (as a detailed text table

and this as a summary

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 3. Re: Calculating the sum of a specific field only when certain fields are unique

I see same number mins for Distinct DIs ID and Service Offer ID

Do you want to report only one value

if thats the case create a calculated field like below and use that in rows instead of Sum(Total Minutes)

it seems it will give the correct O/P

Hope this helps

BR,

NB

1 of 1 people found this helpful
• ###### 4. Re: Calculating the sum of a specific field only when certain fields are unique

May be This?

Thanks

Deepak