Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
Partner
Partner

Aggr, Max and complex if statement in one expression?

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 🙂