Percentage Calculation in Metrics

other Performance Management
1

Hi all,
I need to calculate Percentages - Percentage Metrics.

I have one Measure X.
Measure 2: Y.

But if i do Percentage: X/Y
then in AF i need to use any AGGR function.
What should be done.

I need Sum(X)/Sum(Y). not Sum(X/y)

Is it possible to do metric division in AF.
Ex: (Metric1/Metric2) in AF.

Please help me.

Daisy

Nimu (BOB member since 2004-04-27)

2

Hi D. ,
we resolved that issue mapping into a new Universe the ci_probe.value tables (CI_UNV).
creating an alias of this and link by probe_id.
Create two metrics on the owner subject is propedeutical : Sum(X) and Sum(Y).
Then you can map on new measure object on CI_UNV the Probe_id of X and Probe_id of Y with aggr=none tag, filtering for probe id the first on the native table, and the second on the alias one.
Then you have to manage zero cases…
Then create Metric.
Anyway this is the sql we generate.
select
Ci_probe_value.probe_value/Alias.probe_value
where
ci_probe_value.probe_id= x id and Alias.probe_id = Y id
I hope could be an help for you.

Regards,

NapolitanCustomizer (BOB member since 2004-07-19)

3

We have a number of complex metrics that would be created as multiple data providers (or multiple Selects within 1 data provider) in a regular BO reporting universe. Such as
(table1.fieldA where Flag = N + table2.FieldD where product = X) / table1.fieldB

What I’m seeing from this thread is that the only way to handle this is by creating a universe off the CI tables. Is this correct?

I haven’t seen any info on 6.5. Are there any new features for handling complex metrics?

Thanks,
– Amy

AmyC (BOB member since 2004-02-05)

4

It seems to be the only way, alternately to handle DB objects such as views , materialyzed view that should contain precalculating measures: i advise these last solutions when complex metrics need to be sliced!

Hi,
Flavio.

NapolitanCustomizer (BOB member since 2004-07-19)

5

Flavio,

I am trying to get the % working as you explained but the values I am getting are not right. Can you elaborate on the universe design a little bit for me.

Let me explain what I did.

I have 2 metrics; one for count of cases completed (probe.id = 10) and one for count of total cases (probe.id = 11) and need to get the % of cases completed.

  1. I created a universe that has ci_probe_value table and I created its Alias.

  2. Joined the 2 tables on probe_id (should it be 1-1 or m-m ?) and place a self join on period_date on one of the tables. (This self join is just like a normal AF self join on a date value).

  3. Created an object on probe_value object from one table and used probe_id = 10 in the where clause. This is a dimension object. SHould i make it a measure and apply an aggregation on this??
    Similarly, created an object from the aliased table for probe_id=11

  4. Created a measure object with AGGR=NONE and its select contains

(obj1/obj2)*100

The values that I am getting absolutely dont make any sense what so ever. Please let me know if i am missing an important steps somewhere.

Thanks,

Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

6

Never mind.
Got it to work.

kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

7

Hi
This topic is really interesting. I also encountered a situation wherein i needed a percentage value based on the measures in my universe. My goal is to create a speedometer based on percentage so the boundary would be 0 to 1. I have a question though, why do you need to create an alias on the ci_probe.value and create a self join? What significance does it make, can you not call the 2 ci_probe id’s (queries) in one ci_table and all the records would be pulled? i am just curious.

thanks,
antzcp04

antzcp04 (BOB member since 2006-08-04)

8

can someone please explain this method a bit further or link to somewhere I can find more details? I really need to get this working, but I can’t get the percentage working correctly, specially when it comes to calculating the percentage for sliced metrics… :hb:

J0sh :australia: (BOB member since 2006-10-09)

9

Hi kashif
How did you managed to this do work the percentage problem?
Thanks

Rameshkumark (BOB member since 2002-08-29)

10

One problem , u r hard coding metric ID here.If we delete the first metric this will create problem :roll_eyes:

Jais :india: (BOB member since 2008-01-05)