Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tomovangel
Partner
Partner

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

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

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

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

tomovangel
Partner
Partner
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
Partner
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..