Hello everyone, set Analysis on Qlik (Qlik Sense on Prem, Qlik Sense SaaS, and QlikView) plays a crucial role. It allows users to specify a scope for a metric, depending on user-defined selections.
Until now, the filter conditions were nested within aggregation functions. The following case supplies the total quantity of salesfor the year 2023:
Sum( {<year={2023}>} sales)
The limitation, however, was that the set’s scope was necessarily stored as a primary element. For instance, calculating the number of sales for the previous year or a specified agency required the creation of a new dedicated primary element. Moreover, using multiple aggregations involved repeating the same filter criterion in every formula.
Luckily, Qlik’s August 2022 version introduced an innovative way to express Set Analysis – outside the aggregation function. Users can now write set expressions as follows:
{<year={2023}>} Sum(prix_ventes)*Sum(quantite)
Additionally, they can invoke and filter a primary element in this manner:
{<year={2022}>} [Master Measure]
Scope and Inheritance
Composing an expression outside an aggregation function affect the entire expression unless it is encapsulated in parentheses, enabling the modification of specific only aggregations. For instance, in the following expression, the avg() function is not affected by the year 2022 filter:
({<year={2022}>} Sum(mesure1)/ count(mesure2)) – avg(mesure3)
Internal Set Expression
When an aggregation function already incorporates a set expression, this will be combined with the outer context. But the same merging rules will apply as before.
{<country={"france"}>} Sum ( {$<year={2023}>} sales)
Note: This indicator returns the sales of 2023 consultants for all countries as the exterior filter is not taken into account due to the presence of the $ set identifier.
{<country={"france"}>} Sum ( {<year={2023}>} sales)
Note: This indicator will return the sales of 2023 for france country only, as there’s no set identifier within the aggregation function.
Aggregation Inheritance
The set expression of an external aggregation will never be inherited by an internal aggregation. However, a set expression located outside an external aggregation will be passed on to both the internal and external aggregations. Here are two examples to explain this:
Sum ({< set_analysis1 >} Aggr ( Count ({< set_analysis2 >} Field )))
Here, set_analysis1 will not be added to the set_analysis2
{< ExteriorSetAnalysis >} Sum ({< set_analysis1 >} Aggr ( Count ({< set_analysis2
In this case, ExteriorSetAnalysis will affecte set_analysis1set_analysis2
This enhanced functionality is available across all editions of Qlik Sense (On-premise, SaaS, desktop) from the August 2022 version onwards and also for the versions of QlikView from may 2023. This progress has greatly improved the strength and flexibility of Set Analysis in Qlik applications. It makes it easier for users to define criteria while still keeping advanced aggregation methods.
