Experssion

[upaliwije]

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

[Qrishna]

Sample data please

[MK_QSL]

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

You want to show all three years or not?

[upaliwije]

all 3 yaers

[sunny_talwar]

May be try this

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

[upaliwije]

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

[sunny_talwar]

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