Finding Average of Count per Group 1

[southbean]

Hello All,

I thought this was going to be a fairly simple formula but, I’m having real difficulty with getting an average of a group sum.

I have two groups.

1. ‘Business_Unit’
2. ‘Year’

In the ‘Year’ group I have a sum (total count) of all requisitions per year based on a request date.

In the ‘Business_Unit’ group I have a sum (total count) of each ‘Year’ group total.

For example:

Total Reqs for BU 1: 3,404 	Avg. Reqs per Year: ?

Total Reqs in 2000: 7	
Total Reqs in 2001: 119	
Total Reqs in 2002: 1,400	
Total Reqs in 2003: 1,878

I want to get the average reqs per year (in the above example 851) in the BU group section. All formulas I’ve tried give me an error message along the lines that the sums I want to use can’t be summed.

Please HELP!

Any/all comments or suggestions will be greatly appreciated!!!

- Tom

 

[BobSuruncle]

You've already got the total Reqs.
Sum({Table.Reqs},{Table.BU}).

Now you just need the DistinctCount of the years for each Business Unit.
DistinctCount({Table.Year},{Table.BU})

If DistinctCount({Table.Year},{Table.BU}) <> 0
Then Sum({Table.Reqs},{Table.BU})/ DistinctCount({Table.Year},{Table.BU})


Bob Suruncle

 

[southbean]

Bob,

Thank you for your reply! However, your formula(s) does do not seem to work for me.

Here’s what I’ve got.

Group 1 (Business Unit) is based on a database field {ReqTable.Business_Unit}

Group 2 (Year) is based a formula “@Year_Group”: Year({ReqTable.ReqDate})

In GH1 I’ve got this aggregate formula: Count({ReqTable.ReqID},{ReqTable.Business_Unit})

In GH2 I’ve got this aggregate formula: Count({ReqTable.ReqID},{@Year_Group})

I need to get an average of the yearly Business Units counts.

Total Reqs for BU 1: 3,404     Avg. Reqs per Year: ?

Total Reqs in 2000: 7    
Total Reqs in 2001: 119    
Total Reqs in 2002: 1,400    
Total Reqs in 2003: 1,878

Thanks again for your help!

- Tom

 

[BobSuruncle]

Try this formula:

If DistinctCount({@Year_Group},{ReqTable.Business_Unit}) <> 0
then Count({ReqTable.ReqID},{ReqTable.Business_Unit})
\DistinctCount({@Year_Group},{ReqTable.Business_Unit})

Bob Suruncle

 

[southbean]

Bob,

Bingo! You rock!!! That worked perfectly.

Thanks VERY much for your help!

- Tom