Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Experssion

Hi,

In my QV dov I have following Pivot Table

Screenshot_1.png

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

6 Replies
Qrishna
Master
Master

Sample data please

MK_QSL
MVP
MVP

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

You want to show all three years or not?

upaliwije
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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?