Wednesday, 9 July 2014

Set analysis that ignores all selections except a few

How do you get Set Analysis to ignore all selections except for a few fields?

We have a calendar table, linked to a fact table and some selections are made.

We now want to work out a calculation based on the date range selected (eg WTE) - but there is a problem. As the data is linked, our non-calendar selections (in this case employee) are only linked to some of the dates - infact for 2014 only in the month of May!

So any calculations based on number of days, etc will only return what is in the data. Furthermore, using the {1} set analysis to ignore all selections will ignore also the selection in year, but we need that. And what if you have hundreds of fields to list?

I'm looking at tackling this in set analysis specifically, there are other ways to combat the problem (you could solve it with data islands, or adding extra blank lines in the data)

Method #1 - List all your non-calendar items in the set analysis:

=sum({<Employee=,Department=,Directorate=,Country=,JobTitle=>} WorkingDay)

This is only convenient if you have a few fields, but by far the simplest way to do this!

Method #2 - Use the system fields to generate a list of columns to exclude 

=concat({<[$Table]-={'CalendarTable'}>} distinct [$Field] & '='   ,   ','   )

The formula above will generate a list of fields that are separated by "=,". Save this to a variable then use the variable in your chart expression using the dollar sign expansion to execute the resulting list of fields as code.

=sum({<$(vListofFields)>} WorkingDay)

How does it work? The system fields $Table and $Field are a hidden table in Qlikview that lists the Table and field names for the application. Querying it as if it's a real table - ie using set analysis to limit the items only to the tables we are interested in, then adding a function to format the returned strings, results in the list of fields we are interested in.

You can also tweak the code to list all fields apart from a specified few by listing them in the set analysis:

=concat({<[$Table]-={'CalendarTable'},[$Field]={'Holiday'}>} distinct [$Field] & '='   ,   ','   )

Method #3 - Save your Calendar selections to a variable then apply them to the set analysis, using {1} to ignore all selections

This is similar to method 2, except that we are now generating selections to apply back to section access.

In this instance we will save the selections in each field that we wish to retain into a variable, then apply them back to the field in the set analysis so that it is not lost when the {1} is applied:

vMonthSlns  =if(GetSelectedCount(Month) , '{"'&concat(distinct Month,'","')&'"}')
vQtrSlns =if(GetSelectedCount(Quarter) , '{"'&concat(distinct Quarter,'","')&'"}')
vYearSlns =if(GetSelectedCount(Year) , '{"'&concat(distinct Year,'","')&'"}')

These check that a selection has been made, then return a bracketed list of the options: {"March","May"}

I haven't used GetcurrentSelections() as this can be limited by number- for large numbers this returns a list (eg "23 out of 100") but this is also an option.

Apply this in the set analysis to each field that needs the selections ignoring:

=sum({1<Month=$(vMonthSlns),Quarter=$(vQtrSlns),Year=$(vYearSlns)>} WorkingDays)

Hope this is useful!



  1. Very nice work, you should share this with your erstwhile work mates! :o)

  2. This is useful :)

  3. For method 3 you can also use the P() function which returns the selected fields.

  4. Oh my... :-O

    I've been looking for an elegant solution like this to my recurrent problem with flexible dates and previous period calculations (dates may be shared between contiguous years, what makes the Year-1 approach useless) when there are random "date holes" in the data set and... It works perfect with my p() function!!

    Thanks, thanks, thanks!!! :-)