Set analysis is a way to define an aggregation scope different from
current selection. Think of it as a way to define a conditional
aggregation. The condition, however, is in itself like a selection that
is evaluated before the cube (the chart) is expande...
read more
I have a question regarding to: "Do not use single quotes here! Single quotes should denote literals, i.e. explicit field values. (Yes, today single quotes work as a search, but this is a bug that one day will be fixed…)"
I am just going through Advanced Topics in Design and Development course book and I found the following there:
String/Numeric values
To select string or numeric values, place the value inside {} and single quotes (double quotes also work, but using them is not best practice).
In all other places in QlikView/Qlik Sense, single quotes mean literals and double quotes mean something else. The intention for Set Analysis was when it was designed, to follow the same logic. However, the implementation today is that single or double quotes doesn't matter in Set Analysis. But there is a consensus in the Qlik development organization that this is a bug that should be corrected.
For the above examples, this means that
=Sum({$<Year={'2011'}>} LineSalesAmount)
=Sum({$<Country={'USA'}>} LineSalesAmount)
are both correct. Both 2011 and USA are literals, so single quotes are correct, if you want a case sensitive comparison.
The third example is correct if you compare how QlikView de facto works, but incorrect given the design intention. Our education department has (correctly) described how it works, but not how it should work.
I'll talk to our education department and make sure the training material is changed.
A million Thanks for your wonderful insights. This is a very important and complicated concepts and through examples you have made it clear. I highly appreciated it.
I hope to stay in touch for Data Modeling, Incremental load among other challenging questions.
I have just a little doubt about how Qlik constructs the global set of data before raise the cube.
Let’s say for instance, that we have a straight table with many expressions. Each one of them have their own set analysis which may be different one from another in some way.
So, When you say: “This aggregation function is evaluated globally, before the cube is expanded”. Do you mean that Qlikview makes the union of all differents sets obtained from each set analysis definition (inside every single aggregation expression from the chart) in order to obtain a global set of data before expand de cube?
Yes, sort of... In a way, the calculations need to be made on the union of the sets. However, it is slightly more intricate, and not so simple...
If you have two calculations: Sum({BM1} Sales) and Count({BM2} Products), then you have two aggregation functions that operate on two different tables: "Sales" is typically in the fact table, and "Products" is in a dimension table. This means that the two aggregations are independent and not affected by the set in the other aggregation. For example, the count of products does not depend on BM1. Hence, two separate aggregations.
But if both aggregations are calculated from the same table, then it is optimized so that only one pass is made over this table, and both sets are taken into account.
.png "Former Employee"){kind=icon}
