Aggr/Rank Function query

[sijojoseph22]

Hi All,

We are  having the below data (sample )

Quote Reference	UMR	Sub COB	Insured	Amount
Q1	UMR1	Off-shore	COMPANY1	137555.6
Q2	UMR2	Off-shore	COMPANY2	250019.2
Q3	UMR3	Off-shore	COMPANY3	105113.1
Q4	UMR3	Off-shore	COMPANY2	19775.59
Q1	UMR3	Off-shore	COMPANY3	105019.2

We need to derive the Max Amount and Average Amount group by UMR and Insured and the records will look like the below.

Max Insured (the Insured having the hight amount by grouping UMR and Insured)	Avg Line Size	MAX
COMPANY3	154370.7	210132.3

Calculation given below

Insured	UMR	Amount by UMR and Insured
COMPANY1	UMR1	137,556
COMPANY2	UMR2	250,019
COMPANY2	UMR3	19,776
COMPANY3	UMR3	210,132	Highest

Attached the qvw with sample data.

Thanks,

Sijo

[Anonymous]

try this as a expression?

=max(aggr(sum(Amount), UMR))

or

=aggr(avg(Amount), UMR)

[Anonymous]

What you want to do with Rank()?

check this?

Top 5 analysis using Rank fuction

[sunny_talwar]

How is 210,132 bigger than 250,019?? Not sure I understand what you are trying to achieve

[Anonymous]

you taken the straight table

Try this expression in expression

aggr(avg(Amount),UMR)

[avinashelite]

//Achieving using script

Temp:

LOAD [Quote Reference],

UMR,

[Sub COB],

Insured,

Amount

from

tablename;

result:

LOAD Insured,

sum(Amount) as Sum_Amount,

Avg(Amount) as Avg_amont

resident

Temp

Group by Insured;

In the front end add the Insured, Sum_Amount and Avg_amont to straight table and then

go to chart properties > dimension limits >select the  Sum_Amount column and limit it to 1 .

// in front end

if(rank(aggr(Amount,Insured),2)<=1,Insured)

[sijojoseph22]

Sorry for marking it wrongly.

We are trying to get the below record.

i.e the to display the Max insured by UMR and Insured. and also the Max amount and average by UMR and Insured

Max Insured	Avg Line Size	MAX
COMPANY2	154370.665	250019.2

Thanks in advance.

[sunny_talwar · Accepted solution]

Check this out:

Expressions:

Avg: =If(Max > 0, Avg(TOTAL Aggr(Sum(Amount), UMR, Insured)))

Max: =Sum(If(Aggr(Sum(Amount), Insured, UMR) = Max(TOTAL Aggr(Sum(Amount), Insured, UMR)), Amount))

[sijojoseph22]

Thanks