I must be the last person on Earth to blog about Outer Sets, a new syntax in Qlik expressions that was added in recent editions of the tools. After two wonderful blog articles by HIC and Rob Wunderlich, what’s there to talk about? Let’s see if we can uncover some hidden treasures anyway.

So, what are Outer Sets, anyway? If you were too busy and missed the big news altogether, here is the simple definition. Your Set Analysis expression can now be placed outside of the aggregation rather than inside of it. So, in simple terms, instead of this:

sum( {<_CYTD_Flag = {1}>} [# Amount])

now we can write this:

{<_CYTD_Flag = {1}>}   sum([# Amount])

Well, not as sexy as you’d think, at first glance, right? Let’s peel the onion a bit, shall we?

Outer Sets and Variables

Many of us certified Qlik addicts with some tenure fancy using variables to store our expressions. So, for a simple Sales calculation, we would store an expression like this:

set exp_Sales = ‘ sum([# Amount])’

Needless to say, the real world definition of sales is far from being so simple… But then, we need to calculate this year sales, compared to prior year sales, and this month sales, compared to prior month and to the same month prior year, and then quarters, and rolling 12 months. Before you know it, you have over a dozen similar expressions describing Sales. All of them are quite similar, except for the Set Analysis conditions that we use. You do use Set Analysis for point in time comparisons, don’t you? Because if you still use the dreaded IF() conditions, then you should stop reading this blog, leave and never come back again.

Just kidding of course. In reality, you should hurry up and book your flight to New Orleans, Louisiana, to listen to my lecture on Set Analysis and AGGR at the Masters Summit for Qlik, the best master training money can buy. But I digress…

So, we end up with a dozen or so Sales expressions, describing sales for various time periods. But that’s not it. After sales, we need to do the same for Costs, Margins, Margin percentages, and for many other expressions that need to be calculated in our apps. Let’s say, for example, that we have 12 time periods and 10 expressions – that would result in 120 different formulas, stored in 120 variables. If you ever listened to my Performance Tuning session at the Masters Summit for Qlik, you might remember my golden rule for good performance – “Everything is good in moderation”. Well, storing 120 variables for just a few simple formulas is certainly not THAT…

Let’s review an example from my book QlikView Your Business .

The formula for Direct Customer Profit Margin includes Sales, Cost of Goods Sold (material and labor, but not overhead), and Credit Memos (those include various charges that retailers like to use in order to get some of the money back from their suppliers:)

Direct Customer Contribution Margin = sum([# Amount]) – sum([# CM Amount]) – sum([# COGS – Material]) – sum([# COGS – Labor])

We can simplify this formula if we break it up into individual parts and store each part in a separate variable, like this:

set exp_Sales = ‘sum([# Amount])’;
set exp_CM = ‘sum([# CM Amount])’;
set exp_Cost_Material = ‘sum([# COGS – Material])’;
set exp_Cost_Labor = ‘sum([# COGS – Labor])’;

Now, the formula for Direct Customer Contribution Margin could look a bit simpler:

set exp_Direct_Customer_Contr_Margin = $(exp_Sales) – $(exp_CM) – $(exp_Cost_Material) – $(exp_Cost_Labor);

Furthermore, the formula for the margin % can reuse already existing variables:

set exp_Direct_Customer_Contr_Margin_Percent = ($(exp_Direct_Customer_Contr_Margin)) / ($(exp_Sales))

Slick, isn’t it? Object Oriented at its best! Well, not really. This beautiful structure has to be broken as soon as we need to apply any time-related filters. The same calculation for this year would require injecting Set Analysis filters for this year into every single aggregation, wouldn’t it?

Current YTD Direct Customer Contribution Margin % = (sum( {<_CYTD_Flag = {1}>}  [# Amount])
– sum( {<_CYTD_Flag = {1}>}  [# CM Amount])
– sum( {<_CYTD_Flag = {1}>} [# COGS – Material])
– sum( {<_CYTD_Flag = {1}>} [# COGS – Labor])   )   / sum( {<_CYTD_Flag = {1}>}  [# Amount])

Back to square one… Each formula has to be repeated completely, with another Set Analysis filter. Well, not anymore! This is where the new Outer Sets feature begins to look sexier and sexier:

Current YTD Direct Customer Contribution Margin % = {<_CYTD_Flag = {1}>}  $(exp_Direct_Customer_Contr_Margin_Percent)

Prior YTD Direct Customer Contribution Margin %      = {<_PYTD_Flag = {1}>}  $(exp_Direct_Customer_Contr_Margin_Percent)

Voilà! The same variable is used in all time-related calculations, so now instead of 120 variables we can just store 10 variables and use them over and over again with our 12 time filters.

Outer Sets and Master Measures

Now let’s see what can be done with Master Measures. Those of us who tried replacing the old-school variables with more modern Master Measures in Qlik Sense faced the same problem. It’s not enough to define a Master Measure for the Margin. We have to clone the same measure a dozen times, to create similar copies for the Margin this year, and last year, and this month, and prior month, and then the % change from this year to last year, and more and more and more.

Master Measures are great! They offer numerous advantages and benefits, but they come with some new challenges as well. The old-school variables could be defined in a text file, and we could use the best text editors to maintain them. Master Measures, however, need to be maintained in a narrow sidebar in your Qlik Sense sheet editor, and that, as I’m sure you know, is a whole different experience. When you have hundreds of Master Measures, you have to give them long descriptive names, and then those long names cannot be fully displayed in the narrow sidebar, so finding the relevant Master Measure that holds “Current YTD Direct Customer Contribution Margin % ” could be a bit challenging. However, I digress…

What about Outer Sets and Master Measures? Well, outer sets can be used with Master Measures, and that makes them even more powerful!

If you created a Master Measure and you named it [Direct Customer Contribution Margin %] , now you can apply any of your 12 time-related filters to get the Margin for this year, prior year, this month, etc.  Just like this:

{<_CYTD_Flag = {1}>} [Direct Customer Contribution Margin %]
{<_PYTD_Flag = {1}>} [Direct Customer Contribution Margin %]

Now, this comes with a trade-off. Using these expressions in chart measures means that we don’t enjoy the full benefits of using Master Measures. We’d only be using the formula from the Master Measure, but nothing else. In order to continue using Master Measures, we would still have to define the 12 flavors of the same, but this time they can all reference the “base” Master Measure, thus simplifying your maintenance in case your business users come up with another manual exception for your calculation of sales…

Which way is better? Well, it depends… Pick your poison, folks. You can shorten the list of Master Measures at the cost of losing some of their convenient features, or you can keep the same number of Master Measures, but simplify your maintenance by a lot. Either way, you win something, comparing to the old ways.

Outer Sets and QlikView

The Qlik Design blog states the following: “This change affects all Qlik Sense editions from the August 2022 release. It will also be included in the next major QlikView release, planned for late spring 2023”. And here I have to digress… I grew up in the Soviet Union, where I learned to get creative with scarce resources and not to trust any printed words. So, I had to test this awesome new functionality in QlikView (ver. May 2022). Much to my surprise, it actually works! The Expression Editor shows an error and underscores the whole formula with the wiggly red line, but the chart produced correct numbers, very much as expected:

So, this is it, folks. This new syntax is wonderful, both in QlikView and in Qlik Sense. You can simplify your expressions, improve maintainability and add overall awesomeness to your already awesome applications. Now clear your schedule and come to the Masters Summit in New Orleans on November 14-16. You will learn a lot, I promise. Plus… c’mon, it’s NOLA!