Aggr, Max and complex if statement in one expression?

[tomovangel]

Hello, guys I have been strugling to turn the following requirement into an expression in qlik sense.

I have to calculate the days that a customer is in delay( i got this),

and if the customer has 1 or more loans, all loans except closed ones should be reported with the maximum delay that all loans of the client has.  

So if a customer has 2 open loans, 1 with 50 days in delay and the second with 70 days in delay, both should be reported with 70. 

But if the customer has 2 loans, 1 open and 1 closed, it has to take only the amount from the open loan. 

I have the following fields: 

daysprincipal- amount to be paid

pdsum- amount paid 

EndDate- Date chosen by the client

daysdate- the last payment date

client_pin - client id. 



So I have to calculate, the maximum days in delay for each client. which works with the following formula:

max(aggr(nodistinct max(if(daysprincipal-pdsum>0.01,EndDate-daysdate)),client_pin))

This formula gives me for each loan, the maximum days in delay, grouped for each client. So if a client has 2 loans, both loans will appear with the same days in delay. 

But,  now I have a different problem, I have to exclude loans, that are closed from this aggregation. 

I am checking if the loan is closed with the following formula : 

FirstSortedValue(status,-loanstatusid)<>'closed' , 

this gives me all statuses, that the last sorted status is 'closed' but when  I include it in the if statement, it doesn't return values.. 

How to Re-Write the expression, so as to NOT calculate closed loans inside the AGGR? 

Thank you for your time 🙂