Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum under special conditions

Hi,

I have the following problem:

I have a table like this:

BorrowerIDAccountIDAccountExposureLtV

11

110040
12210060
13320030
13420020
13540060
1465010
14710060

Now I want to sum the AccountExposure of all Accounts one Borrower has under the Condition that one of the belonging LtVs is greater than 50.

So in this case the result should be (100+200+200+400+50+100) = 1050 and not (100+400+100) 600.

I hope someone can help me and I am looking forward to it.

Best regards

ThorQlik

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Sum({<ID1 = {"=Max(K3)>55"}, Date = {"$(=Date(MakeDate(2015, 10, 31), 'DD.MM.YYYY'))"}, K1 = {'>5000000'}>}K2)


Capture.PNG

View solution in original post

13 Replies
sunny_talwar

Try this:

=Sum({<BorrowerID = {"=Max(LtV) > 50"}>}AccountExposure)


Capture.PNG

Not applicable
Author

Thank you very much sunny.

For the question I simplified my problem. Actually there are a lot more conditions connected with "and"  or  "or" that I have to pay attention to.

So at the Moment my Code for the KPI in QlikSense I want to make out of this looks something like this:

Sum(If( (LtV>50 and CoV <90) or DSCR <70 or Rating <7), AccountExposure,0)

Probably there is a way to do it somehow like you have suggested. Could you tell how I can connect different conditions in the Code you posted?

sunny_talwar

I guess I am not sure how they are needed to be aggregated. What I mean is that you were looking to get Max(LtV) before, does any of the other conditions meet the same criteria? Assuming all of them are Max, may be this:

=Sum({<BorrowerID = {"=(Max(LtV) > 50 and Max(Cov) < 90) or Max(DSCR) < 70 or Max(Rating) < 7"}>}AccountExposure)

Not applicable
Author

I tried your suggestion, but the number I get is much to small.

Maybe this way just the maximum values are summed... But in your example it worked...

The conditions are diferent, Sometimes I Need Min() and sometimes Max(), sometimes just equals"=" an so on.

sunny_talwar

Since I don't know the conditions, we have two options

1) You tell me the conditions and may be provide a sample and I can try showing you how this can be done

2) Figure out how this can be done based on the two responses above

HTH

Not applicable
Author

well unfortunately, the data is confidential and pretty extensive. Therefore it would take some time to prepare a good example.

So first I will try my best to solve it myself. Nevertheless I thank you a lot.

sunny_talwar

No problem at all

Not applicable
Author

Example_App.PNG

Okay,I guess I will not come to a solution myself.

So I have made an example App. The formulas I used, you can see in the title of each KPI. I also wrote some comments. My question is: Why does the fourth KPI not work ? And is there a way to fix it ?

sunny_talwar

Would you be able to share the qvf file to take a look at this. And also explain the condition in more details here?