Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tomovangel
Partner - Specialist
Partner - Specialist

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

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 !

 

1 Solution

Accepted Solutions
sunny_talwar

This?

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

View solution in original post

12 Replies
tomovangel
Partner - Specialist
Partner - Specialist
Author

If anyone has some ideas please share, I am willing to try everything '_'

tomovangel
Partner - Specialist
Partner - Specialist
Author

@sunny_talwar  Hello Sunny, can you offer some insight ?

sunny_talwar

Would you be able to share a sample where we can see the issue? It might be easier to see the issue and try out few options

tomovangel
Partner - Specialist
Partner - Specialist
Author

Unfortunatelly no, the system is made so no exports are available so I can't make even a simple excel file with some data inside it.. 

 

 

sunny_talwar

Some mocked up data directly pasted here? All I want to see is what exactly are you looking to do

tomovangel
Partner - Specialist
Partner - Specialist
Author

Okay, I will try to do it in the next 30 minutes thanks for the answers beforehand Sunny ) 

tomovangel
Partner - Specialist
Partner - Specialist
Author

Hello Sunny, attached you will find some mockup qvf 

 

the goal is to select ReportDate( in this case its only 1), and 2 values in statusrs. 

Currently if you select 1 value and check click the button inside the straight table it will select all reference numbers with this status up to report date. But i need to be able to select more than 1 status. 

BR, Angel

sunny_talwar

Which status do I need to select? I am seeing - for any status that I select?

tomovangel
Partner - Specialist
Partner - Specialist
Author

Yes Sunny, The goal is to select 2 or more statuses. 

For Example, normal and defaulted_not_in_collection 

and then I can't make the formula to read both..