Multivalue and calculation of % in an Universe object

[Former Member]

Hello,

I want to calculate a calulated measure in the Universe with the formula

((f_direct_metrics.incurred_loss_amt_ytd/f_direct_metrics.direct_earned_premium_amt_ytd) *100,0)

I was thinking that I will get a percentage values since I am multiplying by 100. When aggregation level is set to NONE, I get MULTIVALUE#, when it is SUM the figure is too large and does not represent a % less than 100. What aggregation should I use.

I plan to use this calculated measure in the Xcelsius dashboard using QAAWS

[Former Member]

Hi Patil,

I had included this in your other thread.

(CASE WHEN SUM(f_direct_metrics.direct_earned_premium_amt_ytd)=0 
THEN 0
ELSE SUM(f_direct_metrics.incurred_loss_amt_ytd)/SUM(f_direct_metrics.direct_earned_premium_amt_ytd)
END)*100

Regards

Alan

[Former Member]

hello,

I tried the sum in front of the column names as indicated, however the results were high numbers >100 and did not look like a percentage value. I also tried to Aggregation level of NONE, & SUM also along with the formula you gave,however the numbers are still high.

It is a simple calculation of calucating the percentage of 2 figures (a) & (b)

(a/b)* 100 also (a-b/b)* 100.

In the universe how do you define % measures??

[Former Member]

Hi Patil,

The formula will return numbers higher than 100 if a is greater than b. Can you give some sample of the numbers you are seeing

Regards

Alan

[Former Member]

Hello,

Thanks for following up

Eg) I have the formula as (Incuured Loss /(direct_earnerd_premium)) * 100

If I have 5 rows as follows

Row 1 5 10 20%

Row 2 6 10 30%

Row3 7 10 40%

Row4 8 10 50%

How do you get the overall %

Should I aggregate level be set to AVGERAGE in the universe object level or SUM or NONE or database delegated. (What does it mean?) I do not want to perform any calculation at front end in webi, as I plan to use QAAWS and create a dashboard from the universe directly.

Thanks

Abhijit

[Former Member]

Hi Patel,

If you are using QaaWS your will not get the detail and the totals, just the details.

The average overall is the sum of column a divided by the sum of column b this is not the same as the average of column a/b.

However, if you use the formula it should always produce the correct results in the query. It will only be an issue if you try to aggregate it beyond the query. The query should be grouped by your dimensions already so choosing sum as the aggregate level will will not affect the outcome.

Regards

Alan

[Former Member]

Hello

SELECT

f_direct_metrics.direct_earned_premium_amt_ytd,

f_direct_metrics.incurred_loss_amt_ytd,

D_MONTH.YEAR,

DV_EXTL_PRODUCER.STATE,

d_line_of_business.line_of_business_id,

(CASE WHEN (f_direct_metrics.direct_earned_premium_amt_ytd)=0

THEN 0

ELSE (f_direct_metrics.incurred_loss_amt_ytd)/(f_direct_metrics.direct_earned_premium_amt_ytd)

END)100*FROM

f_direct_metrics,

D_MONTH,

DV_EXTL_PRODUCER,

d_line_of_business

WHERE

( f_direct_metrics.month_id=D_MONTH.MONTH_ID )

AND ( f_direct_metrics.agency_id=DV_EXTL_PRODUCER.EXTL_PRODUCER_ID )

AND ( f_direct_metrics.line_of_business_id=d_line_of_business.line_of_business_id )

AND

(

( DV_EXTL_PRODUCER.STATE IN @Prompt('Enter Value of State','A','Agency\Agency State',multi,constrained) OR 'ALL' IN @Prompt('Enter Value of State','A','Agency\Agency State',multi,constrained) )

AND

( D_MONTH.YEAR IN @Prompt('Enter Value of Year','N','Month\Year Number',multi,constrained) )

AND

( d_line_of_business.line_of_business_description IN @Prompt('Enter Value of LOB','A','Line of Business\Line of Business Description',multi,constrained) )

So all I am doing is ( A/B) * 100 to get the percentage value. This should be easily achiveble in the Universe right. I do not plan to use webi as I will be using QAAWS, thus I will have to perform the calculation in Excel which will be difficult and should not be the case.

Thnks in advance