topic Re: Making an expression bypass filters in QlikView

Making an expression bypass filters

Hypersnooze — Sat, 16 Nov 2024 01:50:49 GMT

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.

Re: Making an expression bypass filters

Miguel_Angel_Baeyens — Wed, 27 Nov 2019 14:34:23 GMT

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".

Re: Making an expression bypass filters

Brett_Bleess — Mon, 09 Dec 2019 22:20:00 GMT

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