Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
tomovangel
Partner - Specialist
Partner - Specialist

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

Hello, guys 
I have the following table : 

Screenshot_13.jpg
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.


1 Solution

Accepted Solutions
ashok_rajm
Contributor III
Contributor III

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.

View solution in original post

8 Replies
ashok_rajm
Contributor III
Contributor III

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
Partner - Specialist
Partner - Specialist
Author

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
Partner - Specialist
Partner - Specialist
Author

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
Partner - Specialist
Partner - Specialist
Author

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
Partner - Specialist
Partner - Specialist
Author

 

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