Qlikview Set Analysis

Collection analysis

Collections are available for aggregate functions. Aggregate functions are typically used to aggregate a collection of possible records that are defined by the current selection scope. However, an alternative record collection can be defined by a collection expression. Therefore, the collection is conceptually similar to the selection scope.

In use, the set expression always starts and ends with a curly brace, for example: {BM01}.

Collection identifiers

You can use a constant to represent the recordset 1. A recordset represents a full collection of all records in an application.

The symbol $ represents the record within the range of the current selection. Therefore, stating the set expression {$} is equivalent to not stating the set expression. More interestingly, {1-$} defines the inverse of the current selection, which means all data that is not included in the current selection.

The selection in the back/forward stack can be used as a collection identifier. For example, the available currency symbol: $ $ represents a previously selected item, which is equivalent to clicking the Back button. Similarly, $_1 represents a forward step, which is equivalent to clicking the Advance button. Any unmarked integer can be used to represent a back and forward action, such as $ A represents the current selection.

Finally, bookmarks can also be used as collection identifiers. Note that only server and document bookmarks can be used as identifiers. Either the bookmark ID or the bookmark name can be used, such as BM01 or MyBookmark. Only the selected bookmarks section is available. Does not include a value. Therefore, it is not possible to use input fields in bookmarks for collection analysis.

Example:

SUM ({$} Sales)
It can return the sales of the current selection, that is, as SUM (sales).

SUM ({$} Sales)
It can return the previously selected sales.

Sum ({$_2} Sales)
It returns the next two selected sales, which is two steps forward. This is only used if you perform a two back operation.

Sum ({1} Sales)
It returns the total sales in the application, ignoring the selection instead of the dimension. For example, if you use a product as a dimension in a chart, each product will get a different value.

Sum ({1} total Sales)
It returns the total sales in the application, ignoring the selections and dimensions. That is, like sum (all Sales).

Sum ({BM01} Sales)
It can return the sales of the bookmark BM01.

SUM ({$} Sales)
It can return the sales of the bookmark MyBookmark.

Sum ({server\bm01} Sales)
It returns the sales of the server bookmark BM01.

Sum ({server\bm01} Sales)
It returns the sales of the document bookmark MyBookmark.

Set operators

Some set operators can be used in an existing set expression. As described above, all set operators use the collection as operands and return the result in the form of a collection.

+ set operator. This binary operation returns a collection of all the records in the two set operands.

Troubleshooting This binary operation returns a collection of records that belong to the first collection operand but not to another collection operand. If used for unary operations, the result is a complement set.

* Intersection operator. This binary operation returns a collection of two collection operands that comprise a common record.

/symmetric difference set (XOR operator) operator. This binary operation returns a collection that is contained in two collection operands, but does not consist of records that are common to them.

Operator precedence is: 1) Unary negative operator (complement), 2) intersection and symmetric difference set operator, 3) and exclude operator. Within the same group, the expression is judged from left to right. The non-priority order can be determined by the standard brackets, which may be necessary because the set operators are not exchangeable, for example, A + (B–C) differs from (A + B) –c, which in turn differs from (A–C) + B.

Example:

Sum ({1-$} Sales)
It can return all sales except the current selection.

Sum ({$*bm01} Sales)
It can return the intersection of the sales that are included in the current selection and the bookmark BM01.

Sum ({-($+BM01)} Sales)
It can return sales that are not included in the current selection and bookmark BM01.

Attention!
This situation should be avoided when the set operator is used in conjunction with a base aggregation expression that includes multiple Qlikview table fields, which can cause unpredictable consequences. For example, if the quantity and price of two fields come from different tables, you should avoid using the expression sum ({$*bm01}quantity*price). Collection modifier

You can modify the collection by adding or replacing selections. Such modifications can be written to the collection expression. The collection modifier consists of one or more field names, each of which has a selection that falls within the category of this field, all of which are enclosed by < and >. For example:. Field names and field values can be referenced as usual, for example: <[sales region]={' West coast ', ' South America '}>.

Here are a few ways to define the selection range: A simple practice is to define the selection range based on the selected values of the other fields, for example:. Modifier will get the selected value from DeliveryDate and apply it as a selection to OrderDate. If the field includes many different special values (hundreds of), the operation is CPU intensive and should be avoided.

However, the most common choice is to extend the field values inside the curly brackets, separated by commas, as follows:. The wave brackets define the set of elements, which can be either a field value or a field value search. A search is usually defined by double quotation marks, for example: It will select all the ingredients that include the string ' garlic '. The search is not case-sensitive and will occur on values that are not included.

Blank element Set, obvious examples such as, not obvious examples such as (does not search for matching records), these mean that there is no product, that is, its resulting recordset is not related to any product. Note: Settings cannot be created by normal selection, unless selected in other fields, for example: TransactionID.

Finally, for field input mode, it is still possible to force exclusion. If you want to force a specific field value to be excluded, you need to add "~" to the field name.

The collection modifier can be used in the collection identifier, or it can be used alone. It cannot be used for set expressions. When used in a collection identifier, the modifier should be written out immediately after the collection identifier, for example, {$}. When used alone, it is equal to modifying the current selection range.

Example:

Sum ({1} Sales)
It returns sales for the US area, ignoring the current selection.

SUM ({$} Sales)
It returns the sales for the current selection, but removes the area selection.

Sum ({} Sales)
Returns the same amount of sales as the previous example. When the setting modification is omitted, the $ is assumed.

Attention!
The syntax of the previous two cases is interpreted as "no selection" in the area, meaning that all regions can be given other choices. It is not equivalent to a syntax that is interpreted as having no region (or a set of blank elements by default for any text to the right of the equals sign).

SUM ({$} Sales)
It returns the sales for the current selection, but has a new selection of year in region.

Sum ({$<~ingredient = {"*garlic*"}>} Sales)
It returns the sales of the current selection, but forces all raw materials containing the ' garlic ' string to be excluded.

SUM ({$} Sales)
It returns the sales for the current selection, but should select all the years that begin with the digit "2" in the field "year", for example, 2000 and subsequent years.

SUM ({$} Sales)
As in the example above, but including 1980.

SUM ({$1978<2004 "}>} Sales)
As in the example above, but contains a numeric search to specify any range.

Collection modifiers and set operators

As mentioned above, a selection in a field can be defined using a set operator and can work in a different set of elements. For example, the modifier means that the year starting with "20" will be selected in addition to the "1997" year, excluding "2000".

Example:

SUM ({$} Sales)
It returns the sales for the current selection, but adds "OurProduct1" to the selected product list and removes "OurProduct2" from the product list.

SUM ({$} Sales)
It returns the sales of the additional selections in the current selection and field "year", i.e. 1997 and all years beginning with "20", but excluding 2000. Note: If the current selection contains 2000, it will also be included after the modification.

SUM ({$} Sales)
It returns the same amount of sales as the previous example, even if 2000 is included in the current selection initially, it will also be excluded. This example shows the importance of using parentheses to define the precedence order.

SUM ({$} Sales)
It returns the sales for the current selection, but selects all years except 2000 for year, and refers only to the product that contains the string ' bearing '.

Set modifiers use assignment and default collection operators

The above notation defines a new selection scope, ignoring the field's current selection. However, if you want to add a new field value based on the current selection within the field. For example, you might need such a modifier at this point. A short and literal notation is to define the set by default with the assignment operator. Similarly, intersection, complement, and symmetric difference sets can be defined by the assignment operator by default as "*=", "–=" and "/=".

Example:

SUM ({$} Sales)
It returns the sales for the current selection, but uses the default set to add the product "OurProduct1" and "OurProduct2" to the selected product list.

SUM ({$} Sales)
It returns the sales of the current selection, but uses the default set of additions to add in the selection: 1997 and all years beginning with "20", but excluding 2000. Note: If the current selection contains 2000, it will also be included after the modification. As

SUM ({$} Sales)
It returns the sales for the current selection, but only the intersection of the currently selected product and the "OurProduct1" product.

Collection modifiers and currency symbol extensions

Variables and other currency symbol expressions can be used in set expressions.

Example:

SUM ({$} Sales)
Returns the sales for the previous year associated with the current selection. Here, the variable vlastyear that contains the relevant year is used in the currency symbol expression.

SUM ({$} Sales)
Returns the sales for the previous year associated with the current selection. Here, the currency symbol expression is used to calculate the previous year.

Collection Modifiers and advanced search

An advanced search using wildcards and aggregations can be used to define collections.

Example:

Sum ({$–1} Sales)
It returns the sales of the current selection, excluding the trade for products that contain ' Internal ' or ' domestic ' in the product name.

SUM ({$} sales) > 1000000 "}>} sales)
It returns the sales for the current selection, but has a new selection in the field "Customer": Select only customers with a total sales of more than 1000000 over 2007 years.

Set modifier with default field value definition

In the above example, all field values have been explicitly defined or defined by the search. However, you can also define a collection of field values using nested collection definitions.

In this case, you must use the element function P () and E () to render the set of elements and the field exclusion values for positive values, respectively. Within parentheses, you can specify a collection expression and a field, such as P ({1} Customer). These functions cannot be used in other expressions:

Example:

SUM ({$} Customer), Sales)
It returns the sales for the current selection, but is limited to customers who have purchased ' Shoe '. The Element function P () returns a list of possible customers, that is, a list of customers that are implied by the "A" in the Product field.

SUM ({$}), Sales)
The result is as above. If the field in the Element function is omitted, the function returns a positive value for the specified field in the external task.

SUM ({$} Supplier), Sales)
It returns the sales for the current selection, but is limited to vendors that have provided ' Shoe '. The Element function P () returns a list of possible suppliers, that is, a list of customers that are implied by the "Shoe" in the Product field. The list of suppliers is then used as a choice in the field Customer.

SUM ({$}), Sales)
It returns the sales for the current selection, but is limited to customers who have not purchased ' Shoe '. The Element function E () returns the list of excluded customers, that is, the list of customers excluded by selecting "Shoe" in the Product field.

Qlikview Set Analysis