cancel
Showing results for 
Search instead for 
Did you mean: 

How to create a weighted average for ratio ?

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi,

By default for measure it will sum or avg as a group by function in universe. si In the measure object properties make it was none and at reportig level we can set that what ever function we want.

In BOXI r3 you make your measure as delegate mesure insted of measure. It will work I guess.

Cheers,

Suresh Aluri.

Former Member
0 Kudos

Hi,

Try cretaing the variable at report level as:

Sum(A In ([Day]))/Sum(B In ([Day]))

Hope it helps you.

Regards,

Rohit

Edited by: rohit12 on Sep 9, 2009 6:52 AM

former_member207342
Contributor
0 Kudos

Its happening because projection of ABR at reporting side is average. so if if you add only one dimension its calculating average of average in microcube of report.

- Numbers of calls bids (=B)

- Numbers of calls answered (=A)

- ABR = A/B (aggregation is average)

create a measure for ABR (A/B) and in the properties of that measure set the projection as 'None' or"Database delegate"

I hope I understood your problem. give it a try and let me know.

--Kuldeep

Edited by: Kuldeep Chitrakar on Aug 27, 2009 7:21 AM

Former Member
0 Kudos

Dear Kuldeep,

Yes that's excatly the problem.

I tried your proposal but I have a Multivalue error.

Currently we are using BO XI 2 but we will migrate to BO XI 3.1 next week.

I hope it will help

Thanks anyway.

Regards,

Sebastien

i808418
Associate
Associate
0 Kudos

Hi,

Database delegate" is not recognized by Buness Objects Explorer (Polestar).

Any suggestions?

-Mike