Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
Showing results for 
Search instead for 
Did you mean: 
Master II
Master II

Set analysis - ignores filters on all fields but one

Can set analysis ignore filtering on all but a single field?

I know that:


sums all values in Fieldname, ignoring any filtering.

I want a similar expression that will ignore filters on all fields except FilterFieldname.

18 Replies
Contributor II
Contributor II

Please be careful when using this solution. What this FieldToKeep=P(FieldToKeep) means is this:

get all possible values associated with the values in the field FieldToKeep. So if any of the filedsvalue is null, any associated record will be excluded.

for example, you have below record:


1  3  4

2  2  3

2  4

sum({ c=p(c) } B ) will give 5 instead of 9 even without a selection on C.

For this solution to work, ensure that you don't have any null value or null reference to the fact you are aggregating.

Below is a better solution though could be slow for huge datamodel: Ignore all selections except some specific fields using Set Analysis

Contributor III
Contributor III


This is a small add-on to the solution that Matus pointed out.  The article (a great article) he points to seems to use just one set.  I was in a situation where I wanted to ignore selections of all fields from several tables but wanted to exclude a couple fields that are in those tables.  I used the following:



1<$Table =  {'Table1','Table2','Table3'}>    //Set containing the tables containing the fields you want to ignore selections

-    //use the Exclusion set operator to NOT include the next set

<$Field={'Field_to_Exclude1','Field_to_Exclude2'}>    //set containing the "Exception" fields


'[' & $Field & ']' ,'=,') & '='     //adding the [ and ]  brackets around the field name and also adding the equals sign at the end of the last field in the created array

I use this for Date period labels for Expression Labels.  So I first use the above as a vSet_Variable.

Then the label expression becomes:

maxstring({<$(vSet_Variable), Other_restrictions_as_needed_for_custom_time_period }> Month)


Contributor II
Contributor II

Thank you.

Contributor II
Contributor II

Hey guys,

What if I only wanted to ignore the filters on ONE field? 

Partner - Creator III
Partner - Creator III

Sum({<FieldToIgnore>} Sales)


Contributor II
Contributor II

Thanks, but it doesn't seem to do it for me.

I'm using a line:

FirstSortedValue ({<StartDate>} DISTINCT StartDate, PersonID)

Perhaps the bypass only works with aggregations and not 'FirstSortedValue'?

Partner - Creator III
Partner - Creator III


You are quite probably interchanging "FirstSortedValue" arguments. I think you may want to use

FirstSortedValue ({<StartDate>} DISTINCT PersonID,StartDate)

(firts argument: what to show. Second argument: sort criteria)


Contributor II
Contributor II

This formula changed my life!

Partner - Creator
Partner - Creator

Hello everybody, perhaps this can help someone:

sum({$<Year=, Quarter, Month>}LineSalesAmount)

Isn't it?