Skip to main content
Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
Hypersnooze
Contributor II
Contributor II

Making an expression bypass filters

Hi there, I'm quite new to QV but try to get my head around it.

I've got a database where I register people attending various events. Each event has a different date, and some people attend several events. When I wish to export my list of people attending these events, I only wish to show the date of the first event they attended.

So I wrote this line in an expression: 
FirstSortedValue(Distinct StartDate,PersonID)
This line allows me to view the date only of the first event they attended.

However, my problem is that once I start using filters like "Month" or "Year", my diagram only shows the people that attended events during that month.

For example, lets say I have 6 people. 3 of them attended an event in June, but all 6 attended an event in July. That'd make my list show:

without filter:
3 users with 2019-06-01 and 3 users with 2019-07-01.

with filter (lets say for July)
3 users with 2019-07-01.

However, when showing people for July I still want the 3 persons showing from June, as they too attended the event in July. But when I use filter for July, those 3 people are not showing as their start date was not in July, it was in June. But I want all 6 people showing with the correct start date.

So I messed around with some stuff I found on the forums. Mainly using stuff like
FirstSortedValue({<$StartDate={'Year','Month'}>}Distinct StartDate,PersonID)

But I realize I have no idea of what I'm doing.

The diagram I'm using has 1 dimension and several expressions, but I only want THIS expression to bypass the selected filters (atleast month and year), and instead show the date of which a person attended his/her first event.

I realize all this probably makes NO SENSE what so ever... but I hope anyone can help.

Labels (3)
1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

If that expression works and returns what you want but is affected by any filter (which is expected behavior in QlikView) try by adding

FirstSortedValue({1} DISTINCT StartDate, PersonID)

Note the {1} in there, which could stand for "for this expression, use the full set of rows, regardless any selections".

View solution in original post

2 Replies
Miguel_Angel_Baeyens

If that expression works and returns what you want but is affected by any filter (which is expected behavior in QlikView) try by adding

FirstSortedValue({1} DISTINCT StartDate, PersonID)

Note the {1} in there, which could stand for "for this expression, use the full set of rows, regardless any selections".
Brett_Bleess
Former Employee
Former Employee

Did Miguel's post get you what you needed?  If so, be sure to return to the thread and use the Accept as Solution button on his post to give him credit for the assistance and let other Community Members know this worked.  

All I have in addition to what he provided is a Design Blog link that will give you some further details on Set Analysis:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

Here is the base URL for the Design Blog area too in case you want to do some other searches, this is a good area to search for how-to related items:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.