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 🙂
