Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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:

     sum({1}Fieldname)

sums all values in Fieldname, ignoring any filtering.

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

18 Replies
Highlighted
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:

A B C

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

Highlighted
Contributor III
Contributor III

Hi,

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:

=concat(

{

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)

Enjoy!

Highlighted
Contributor II
Contributor II

Thank you.

Highlighted
Contributor II
Contributor II

Hey guys,

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

Highlighted
Partner
Partner

Sum({<FieldToIgnore>} Sales)

Jaime.

Highlighted
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'?

Highlighted
Partner
Partner

Hi:

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)

Jaime.

Highlighted
Contributor II
Contributor II

This formula changed my life!
LOL

Highlighted
Partner
Partner

Hello everybody, perhaps this can help someone:

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

Isn't it?

Mark