topic Re: Aggr/Rank Function query in QlikView

Aggr/Rank Function query

sijojoseph22 — Thu, 03 Mar 2016 11:50:27 GMT

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

Re: Aggr/Rank Function query

Anonymous — Thu, 03 Mar 2016 11:55:56 GMT

try this as a expression?

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

=aggr(avg(Amount), UMR)

Re: Aggr/Rank Function query

Anonymous — Thu, 03 Mar 2016 11:59:19 GMT

What you want to do with Rank()?

check this?

Top 5 analysis using Rank fuction

Re: Aggr/Rank Function query

sunny_talwar — Thu, 03 Mar 2016 11:59:34 GMT

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

Re: Aggr/Rank Function query

Anonymous — Thu, 03 Mar 2016 12:00:40 GMT

you taken the straight table

Try this expression in expression

aggr(avg(Amount),UMR)

Re: Aggr/Rank Function query

avinashelite — Thu, 03 Mar 2016 12:01:15 GMT

//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)

Re: Aggr/Rank Function query

sijojoseph22 — Thu, 03 Mar 2016 12:36:28 GMT

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.

Re: Aggr/Rank Function query

sunny_talwar — Thu, 03 Mar 2016 13:06:50 GMT

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))

Re: Aggr/Rank Function query

sijojoseph22 — Fri, 04 Mar 2016 10:29:37 GMT

Thanks