How to create a weighted average for ratio ?

701 Views

Hi All,

I have a problem to create weighted ratio. I try to create ratio but as soon as the report

contains less dimensions than the query itself, the aggregation of the ratio is a simple average

instead of a weighted average.

The goal is to present results in differents graphs, table based on a single query.

Here is an example based on monitoring phone calls

I have 2 dimensions and 3 measures defined in the universe

Dimensions

- Day

- Country

Measures

- Numbers of calls bids (=B)

- Numbers of calls answered (=A)

- ABR = A/B (aggregation is average)

1. Working Case

If I create a table with 2 dimensions it's working fine, measures are correct.

The ABR is correct

          | France                    | Italy        
          | A        B     ABR        | A        B      ABR
---------------------------------------------------------------
01/08/09  | 20      30     0.67       | 10     20      0.50
02/08/09  | 10      30     0.33       | 45     60      0.75 
03/08/09  | 15      20     0.75       | 10    100      0.10

2. Error case

If I create a table with only 1 dimension, ABR measure is wrong

          | A        B      ABR       
--------------------------------------
01/08/09  | 30      50     0.59       =(.67+.50)/2       
02/08/09  | 55      90     0.54       =(.33+.75)/2          
03/08/09  | 25      120     0.43       =(.75+.10)/2

I was expecting to have this

          | A        B      ABR       
--------------------------------------
01/08/09  | 30      50     *0.60*      = 30/50   or (30 * 0.67 + 20 * 0.50)/(30+20) 
02/08/09  | 55      90     *0.61*      = 55/90
03/08/09  | 25      120    *0.21*       = 25/120

I know that defining the ratio in the report instead of the universe is working

but I wanted to find a solution that avoid the need of recreate each time the ratio in the report.

Thanks in advance for any idea.

Regards,

Sebastien

Edited by: Anthony Fradera on Aug 26, 2009 5:10 PM