New Set Analysis Syntax

Set Analysis is one of the most powerful features since it allows you to create your own subset independent of current selections. Although Set Analysis syntax is somewhat intimidating for a new Qlik developer, once you master the syntax, it opens a world of possibilities for custom solutions.

That said, for a long time, set syntax resided within the aggregation function. That aspect of set syntax constrained the re-usability of the expression containing set syntax in a master item.

For example, let’s look at the set syntax for USA sales.

sum({<Country = {‘USA’}>} SalesAmount)

If we store this expression in a master measure, Sales, it is not useful to calculate sales for other countries.

Enter new set analysis syntax.

{<Country = {‘USA’}>} sum(SalesAmount)

Now, we can store base aggregation in a master measure, Sales,  with the expression sum(SalesAmount) and use the master measure in a chart expression by adding set syntax on the fly.

{<Country = {‘USA’}>} Sales

Another example. Let’s calculate CYTD – LYTD sales.

A typical set syntax with proper flag fields:

sum({<CYTDflag = {1}>} SalesAmount) – sum({<LYTDflag = {1}>} SalesAmount)

We can re-use the existing Sales master measure and re-write the expression with a new set syntax.

In order to define the scope of the syntax, it’s important to enclose expression within parenthesis. In the previous expression, the same master measure is used in the calculation with parenthesis to compute the sales difference.

If we have a master measure, COGS(Cost of Goods Sold), now we can adapt the new set syntax to calculate margin easily.

Inheritance

It’s worth knowing inheritance rules when multiple expressions are involved.

Rules

1. All expressions without set syntax will inherit set syntax from the context.
2. In order to exclude a specific expression of expressions, use parenthesis around expressions that need inheritance from the context.
3. Any expression with an identifier will not inherit set syntax from the context.

Examples

Assume that there are two master measures.

1. Distinct Customers = count(distinct customer ID)
2. Sales = sum([#Sales Amount])

In addition, there is a flag field to indicate if the customer is new: a _NewCustomerFlag

We can calculate New Customer Count with the following expression:

Now, we can calculate the sales amount by new customers with the following expression:

We can use the first inheritance rule to calculate average sales by new customers with the following expression:

Now, using the third rule, we calculate new sales as a % of total sales with the following expression:

I hope that this new set syntax makes it easier to reuse existing master measures in your future development efforts.

The world is your oyster!

Note: This blog post was inspired by Hic’s recent article on the Qlik Design Blog.

-Shilpan