topic Re: Max/Min with aggr function in QlikView

Max/Min with aggr function

nehasaxena — Thu, 29 Jun 2017 05:39:17 GMT

Hi All,

I am using below expression -

Num(Max(aggr(Sum([Loss Paid Filter])/sum([Written Premium Filter]),LOB,product)),'#,##0.00%')

I am using max function but it is giving me min value.

and with min expression it is giving zero.

Please help me with the correct expression.

Let me know if further details required

Best regards,

Neha

Re: Max/Min with aggr function

Anil_Babu_Samineni — Thu, 29 Jun 2017 05:42:33 GMT

What if you write some thing like below. Does this expression gives output to you?

Num(aggr(Sum([Loss Paid Filter])/sum([Written Premium Filter]),LOB,product),'#,##0.00%')

Would you able to provide snapshot for that data atleast 10 rows

Re: Max/Min with aggr function

Sergey_Shuklin — Thu, 29 Jun 2017 06:07:17 GMT

Hello, Neha!

It's interesting case and it seems like you have your "max(aggr(" case on negative values so "max" is returning the min value, and a "true" max is zero. Maybe there are some format restrictions in the chart (on Number tab) where your format-condition '#,##0.00%' doesn't make a sense?

Re: Max/Min with aggr function

nehasaxena — Thu, 29 Jun 2017 07:19:14 GMT

I observed that this expression is not considering full records. See the image. This table is having expression without aggr. My aggr expression is not considering 2017 Q1 records. I need to modify it.

Any suggestions are welcome.

Thanks.

Best Regards,

Neha

Re: Max/Min with aggr function

nehasaxena — Thu, 29 Jun 2017 07:20:17 GMT

I want to display this expression in text box so i have to use Num formatting.

Regards,

Neha

Re: Max/Min with aggr function

antoniotiman — Thu, 29 Jun 2017 07:41:04 GMT

What is Expression in Your table ?

Re: Max/Min with aggr function

nehasaxena — Thu, 29 Jun 2017 08:08:04 GMT

Sum([Loss Paid Filter])/sum([Written Premium Filter])

Re: Max/Min with aggr function

antoniotiman — Thu, 29 Jun 2017 08:18:59 GMT

Have You tried to add Quarter in Aggr(.......,Quarter) expression ?

Re: Max/Min with aggr function

nehasaxena — Thu, 29 Jun 2017 08:19:29 GMT

Hi,

With Num(aggr(Sum([Loss Paid Filter])/sum([Written Premium Filter]),LOB,product),'#,##0.00%') this expression output is -

LOB	product	Quarter	Loss Ratio
LOB1	P1	Q1	1.40%

and with Sum([Loss Paid Filter])/sum([Written Premium Filter]) this expression output is -

LOB	Product	Quarter	2017Q1	2017Q2
LOB1	P1		208.76%	1.40%

So basically with aggr function results display is wrong. For Q2 value is missing and Q1 has value that is actually for q2.

I am confused with the results.

Re: Max/Min with aggr function

nehasaxena — Thu, 29 Jun 2017 08:25:34 GMT

Yeah thanks for reminding, I missed quarter in aggr expression. But the issue still exist with min aggr. Max aggr is working fine.

Max Aggr Expression -

Num(Max(aggr(Sum([Loss Paid Filter])/sum([Written Premium Filter]),LOB,product,YearQuarter)),'#,##0.00%')

This is giving me correct result - 208.76%

Min Aggr Expression -

Num(Min(aggr(Sum([Loss Paid Filter])/sum([Written Premium Filter]),LOB,product,YearQuarter)),'#,##0.00%')

For this output is 0.00% , actually it should give 1.40%

Re: Max/Min with aggr function

antoniotiman — Thu, 29 Jun 2017 08:34:02 GMT

This is because probably You have any Sum(Loss Paid Filter) = 0.

Try to exclude this value

Sum({<[Loss Paid Filter]-={0}>} [Loss Paid Filter])

Re: Max/Min with aggr function

nehasaxena — Thu, 29 Jun 2017 11:48:19 GMT

Thanks all for response and help. Expressions are working fine. Its just that we have zero and that why min aggr always give 0.

Best regards,

Neha