13 Replies Latest reply: Aug 10, 2016 9:21 AM by Sunny Talwar

# 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

• ###### Re: Sum under special conditions

Try this:

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

• ###### Re: Sum under special conditions

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?

• ###### Re: Sum under special conditions

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)

• ###### Re: Sum under special conditions

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.

• ###### Re: Sum under special conditions

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

• ###### Re: Sum under special conditions

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.

• ###### Re: Sum under special conditions

No problem at all

• ###### Re: Sum under special conditions

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 ?

• ###### Re: Sum under special conditions

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

• ###### Re: Sum under special conditions

okay, I want to sum all K2 that belong to the same ID1, if the Max of all to ID1 belonging K3 is bigger than 55.

Moreover the Date should be the 31th of Oktober 2015 and K1 should be bigger than 5.000.000.

The result for the above fourth KPI should be about 96.642.956.

• ###### Re: Sum under special conditions

Try this:

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

• ###### Re: Sum under special conditions

You are the best. Thank you very much.

Now I have to workout several other conditions, but I know it is possible and hopefully I will get everything done.

• ###### Re: Sum under special conditions

Yup, keep trying and I am sure you will figure it out