Set Modifiers in Qlikview

Set Modifiers Qlikview Introduction

A set can be modified by an additional or a changed selection. Such a modification can be written in the set expression. A set modifier can be used on a set identifier or on its own. It cannot be used on a set expression.

The modifier consists of one or several field names, each followed by a selection that should be made on the field, all enclosed by < and >.

Ex: <Year={2007,+2008},Region={US}>.

When used on a set identifier, the modifier must be written immediately after the set identifier, for examples {$<Year = {2007, 2008}>}. When used on its own, it is interpreted as a modification of the current selection.

The entire set of field-value definitions will be enclosed in angle brackets (< >) and the syntax is FieldName = {NewValue}.

Different variations to this syntax are described as follows:

FieldName = {value}: when the NewValue parameter is a numeric value, it is specified as such with no additional modifications.

FieldName = {'TextValue'}: when the NewValue parameter is text, we should enclose it in straight single quotes

FieldName = {"SearchString"}: if we want to use a search string as the value definition, we should enclose it in straight double quotes.

Basic Syntax:

The following example is used to show how a Set Analysis is composed; Sum of Sales for the country UK to include all current selections.

The field to be used in the expression is SALES and the values in that field are to be added together therefore Sum is used.

Sum(SALES)

The Set portion of the expression is then added immediately after the first parenthesis and is enclosed in curly brackets {set expression}

Immediately after the first curly bracket ({) a $ sign states that the record set will honor existing current selections and a 1 states that all current selections are to be ignored and the set analysis is to be based on a full data set. Since the example requires current selections to be included a $ is used:-

Sum ({$}Sales)

Next the field/s that will be used in the set expression are defined and these are added immediately after the $ and enclosed in less than / greater than (<>) symbols. In this example only records where country equals UK are to be included, the completed Set Analysis statement is:-

Sum ({$ <Country ={‘UK’} > } Sales)

Or it can help, when reading a Set Statement to separate the syntax across multiple lines:-

Sum (
{$
<Country ={‘UK’} >
}
Sales)

Examples:

Standard Sum of field Sales:-

SUM( SALES)

Set Analysis style SUM of field Sales. The $ means that all current selections remain:-

SUM({$}SALES)

Set style SUMof field Sales. The 1 means current selections are ignored:-

SUM({1}SALES)

SUM of Sales for All(*) countries and including current selections:-

SUM({$<[Country] = {"*"} >}SALES)

Sum of Sales for all counties, excluding current selections:-

SUM({1<[Country] = {"*"} >}SALES)

Sum of Sales for United Kingdom andincluding current selections:-

SUM({$<[Country] = {"UK"} >}SALES)

Sum of Sales for All Countries except United Kingdom, including current selections:-

SUM({$ <[Country] -= {"UK"} >}SALES)