topic Re: Experssion in QlikView

Experssion

upaliwije — Sat, 17 Mar 2018 04:01:40 GMT

Hi,

In my QV dov I have following Pivot Table

In the avove example there is a loss in year 2016. I want to show all the policies if total Gross Profit for a particular a policy is negative figure. That is exactly what I have shown above. But if I apply a condition to select loss policies I see records for 2015 and 2017 not showing in my pivot table. Then my epressiin will be

if((sum(PREMIUM)-SUM({<YEAR=>}NET_CLAIM))<0,sum(PREMIUM)).

So what I want is to show records for all 3 years if the Total GrossProft figure is negative. Pls help me modify my expression

Re: Experssion

Qrishna — Sat, 17 Mar 2018 06:24:32 GMT

Sample data please

Re: Experssion

MK_QSL — Sat, 17 Mar 2018 09:55:13 GMT

Sorry but I am confused, what exactly you want to do here?

You want to show all three years or not?

Re: Experssion

upaliwije — Sat, 17 Mar 2018 14:39:13 GMT

all 3 yaers

Re: Experssion

sunny_talwar — Sun, 18 Mar 2018 11:58:27 GMT

May be try this

If(Sum(TOTAL <POLICY_NO, INSURED> PREMIUM) - Sum(TOTAL <POLICY_NO, INSURED> {<YEAR=>}NET_CLAIM) < 0, Sum(PREMIUM))

Re: Experssion

upaliwije — Mon, 19 Mar 2018 02:38:41 GMT

Thanks sunny .

It works. But if I create varivale with that expression it does not work. My variable is

vGP=

(Sum(TOTAL <POLICY_NO, INSURED> PREMIUM) - Sum(TOTAL <POLICY_NO, INSURED> {<YEAR=>}CEDED_PREMIUM)+ Sum(TOTAL <POLICY_NO, INSURED> {<YEAR=>}CEDED_COMM)

- Sum(TOTAL <POLICY_NO, INSURED> {<YEAR=>}COMMISSION) - Sum(TOTAL <POLICY_NO, INSURED> {<YEAR=>}NET_CLAIM))

and in my pivot table when I write the follwing expression it does not work. Pls help

if($(vGP)<0,sum(PREMIUM))

Re: Experssion

sunny_talwar — Mon, 19 Mar 2018 08:54:20 GMT

Not sure... would you be able to share a sample?