How to show results based on expression, and multiple selections on this expression?

[tomovangel]

Hello dear Qlikers, 

I am at a rather interesting crosspoint. 

I have a field, from which user selects a different Status for loans( open,closed,normal etc.) which has around 10 values. 

I need to show, based on a Reporting Date, what is the latest status of each loan, which I do with FirstSortedValue :

FirstSortedValue(DISTINCT {<statusdate={"<=$(=Only([Report Date]))"}>} loanstatus,loanstatusid)

However, my next requirement was to select loans in specific statuses at Reporting date. 

I use this expression as a dimension in a straight table , and it works when there is only 1 selected value in the field 'selectedstatus'.

When the user clicks 'Generate Report' QLik Sense automatically selects all loanid's which have the selected status as their last status:

aggr(if(FirstSortedValue(DISTINCT {<statusdate={"<=$(=Only([Report Date]))"}>} loanstatus,loanstatusid)=selectedstatus, 'Generate Report',loanid))

However, now I need to show results for more than 1 selection, for example the user selects Open and Closed, how can I rewrite the formula so it works, I have looked at examples using Match, wildmatch etc. but I can't seem to get it working.

Your help is kindly appreciated !

[sunny_talwar · Accepted solution]

This?

=Aggr(If(
	FirstSortedValue(DISTINCT {<[Status generated on]={"<=$(=Only([ReportDate]))"}>} Status,-statusid)=statusrs ,'Generate Report for loans')
, [RandomNumber1], statusrs)

[tomovangel]

Thanks Sunny 😉 Seems like you got it to work 🙂 Nice usage of aggr, i didn't know why i hadn't thought of that. 

🙂 

[sunny_talwar]

You were already using Aggr(), all I added was to add statusrs as one of the dimensions within Aggr() function