topic Re: Set Expression in QlikView

Set Expression

upaliwije — Fri, 15 Sep 2017 11:19:36 GMT

Hi Friends

My following expression does not return a value

=SUM(IF(Aggr(SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)/SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM),CRE_NAME_1)>.1,SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)))

Pls advise where I have made the error

Re: Set Expression

swuehl — Fri, 15 Sep 2017 11:23:49 GMT

What do you want to achieve?

Maybe

=IF(

Sum(Aggr(SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)/SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM),CRE_NAME_1)) >.1

,SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)

)

Otherwise there are two issues: the condition is only true if your comparison returns same result for all CRE_NAME_1 and the THEN branch is an aggregation embedded into another.

Re: Set Expression

upaliwije — Fri, 15 Sep 2017 11:36:03 GMT

I want to show in my Pivot table only A,B,F where % is >.1. My Calculation is Individual Fin_premium/ Total FIN_Premium

Re: Set Expression

Kushal_Chawda — Fri, 15 Sep 2017 11:43:18 GMT

also try this

=sum(aggr(IF(SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)/SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)>.1,SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)),CRE_NAME_1))

=SUM(aggr(IF(Aggr(SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)/SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM),CRE_NAME_1)>.1,SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)),CRE_NAME_1))

Re: Set Expression

upaliwije — Fri, 15 Sep 2017 11:49:55 GMT

Both of Your expression works but none meet me condition of .1

Re: Set Expression

swuehl — Fri, 15 Sep 2017 11:51:24 GMT

Won't you Need a TOTAL qualifier in the denominator of your division? Looks like the division is constant 1.

Re: Set Expression

upaliwije — Fri, 15 Sep 2017 11:52:58 GMT

Kindly modify the expression and show

Re: Set Expression

swuehl — Fri, 15 Sep 2017 12:01:14 GMT

Maybe

=Sum(

Aggr(

IF(

SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM) / SUM(TOTAL {<RISK_DATE={$(vP16)}>}FIN_PREMIUM)>.1

,SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)

)

,CRE_NAME_1)

)

Re: Set Expression

Kushal_Chawda — Fri, 15 Sep 2017 12:03:55 GMT

It's not working because you have another expression %, in that expression also you need to write the if condition

aggr(IF(SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)/SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)>.1,

SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)/SUM({<RISK_DATE={$(vP16)}>}FIN_PREMIUM)),CRE_NAME_1)