Problem calculating on Weighted Average on Summary line.

[njvree]

Hello -

I need some help developing a "Revenue weighted duration average" in Cognos and hope you can help.   Basically we are calculating a figure to measure the duration between a service receipt and it being invoiced, weighted by the revenue of the service in comparison of totally monthly revenue.  It can be written numerous ways, but this formula works (in excel at least): sum(revenue*duration)/sum(revenue).  Below is what I have in Cognos. 

'YYYYMMM              '	'Duration                 '	'Revenue                 '	'RevenuexDuration  '
'201501   '	'8             '	'600         '	'4800       '
'201501   '	'4             '	'500         '	'2000       '
'201501   '	'3             '	'900         '	'2700       '
201501	15	2000	9500

The bold line are summary values.  I want to divide the RevenueXDuration summary value by the Revenue summary value [Sum(RevenueXDuration)/sum(Revenue)], however when I throw in a Query Calculation on the summary line to do this, it comes back slightly incorrect .  Like up to 1, but usually a couple tenths off.  I assume it is not performing the calculation I think it should.  E.g. in the example above, instead of getting 4.75 like I should get, i would get something like 4.37.

Can anyone make suggestions of how to do this differently or things I could check that are throwing this off?

[cognos810]

Hello njvree,
The Regular Aggregate and the rollup aggregate property for the data item which contains expression [RevenuexDurantion]/[Revenue] should be "Calculated".
You want the summary row to behave as First sum one column, then sum the next column and then do the divide. Instead what's happening is that the division is done per record and then totaled up for the footer. Hence the numbers mismatch.
Alternatively, create separate summary data items to be placed into the footer and the expression should be:
total(revenueXduration for report)/total(revenue for report)

-Cognos810

[njvree]

Thanks Cognos810.  Getting my aggregrate/rollup aggregrate properties right was the answer I needed to hear ... including putting the final data item to 'calculated' and fixing some other aggregrate properties I had previously messed withup.  Much Appreciated  :)