Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following problem:
I have a table like this:
BorrowerID | AccountID | AccountExposure | LtV |
---|---|---|---|
11 | 1 | 100 | 40 |
12 | 2 | 100 | 60 |
13 | 3 | 200 | 30 |
13 | 4 | 200 | 20 |
13 | 5 | 400 | 60 |
14 | 6 | 50 | 10 |
14 | 7 | 100 | 60 |
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
Try this:
Sum({<ID1 = {"=Max(K3)>55"}, Date = {"$(=Date(MakeDate(2015, 10, 31), 'DD.MM.YYYY'))"}, K1 = {'>5000000'}>}K2)
Try this:
=Sum({<BorrowerID = {"=Max(LtV) > 50"}>}AccountExposure)
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?
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)
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.
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
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.
No problem at all
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 ?
Would you be able to share the qvf file to take a look at this. And also explain the condition in more details here?