Aggr, Max and complex if statement in one expression?

tomovangel — Sat, 16 Nov 2024 07:03:21 GMT

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 🙂

topic Aggr, Max and complex if statement in one expression? in App Development

Aggr, Max and complex if statement in one expression?