how to assign max value of an expression, to it based on aggr?

[tomovangel]

Hello, guys 
I have the following table : 

I could do it in the script, but the user selects dates, and based on this dates the Delay Days are calculated. 
So, I have to think of an expression, which will do the job. 

I have tried some variations of aggr and firstsortedvalue, but nothing works as expected..
I am attaching an excel file with this loan... 

Thanks.

[ashok_rajm · Accepted solution]

Please check the below syntax for the result to show max values for same client.

max(aggr(nodistinct max([Days in delay]),client_pin))

If this is not what you expected then please post what result you would need.

[tomovangel]

Thank you,  the delay days field is a formula, but when I changed it it works as expected 🙂 

[sunny_talwar]

Do you have Days in Delay as a field? if you do, then may be just try this

Aggr(NODISTINCT Max([Day in delay]), client_pin)

[tomovangel]

Thank you for the Answer Sunny, but the question was marked as correct, and once again your answer is correct as well. 

I ended up with using this in a variable :

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

[sunny_talwar]

Sounds good 🙂

[tomovangel]

Hello, Sunny, I have another requirement for this expression. 

I have to evaluate all loans, and if the loan is closed it must be excluded from the calculation. 

I have tried using this formula that I saw you use earlier :
max(
aggr(nodistinct max(
{<ref_nr = {"=Match(FirstSortedValue(DISTINCT {<generated_r1date={""<=$(=Only(EndDate))""}>} status_r1,-loanstatusid), 'closed', 'closed_defaulted')"}>}if(daysprincipal-pdsum>0.01,EndDate-daysdate)),client_pin))

But it doesn't give me correct values. 

Where do you think can be the error?

[sunny_talwar]

Would you be able to share a sample with the output you expect to see?

[tomovangel]

Oh, Okay Sunny, I gave it a little thought, and I just reworked the formula to NOT count these loans like this

max(
aggr(nodistinct max(
{<ref_nr -= {"=Match(FirstSortedValue(DISTINCT {<generated_r1date={""<=$(=Only(EndDate))""}>} status_r1,-loanstatusid), 'closed', 'closed_defaulted')"}>}if(daysprincipal-pdsum>0.01,EndDate-daysdate)),client_pin))

And its working perfectly according to me, I will re-send it to my client and I will see whats'what 🙂 Thank you