Summary: I demonstrate how outer sets in Qlik expressions can be used to extend the reusability of Master Measures.
Without outer sets if we wanted to inject set analysis into a measure, we had to copy the expression text and edit. Consequently we are no longer linked to the master item and have lost the maintenance and quality benefits of being linked.
Let’s look at an example where outer sets can improve reusability. In our sample scenario, the business rule for “Sales” is sales receipts minus returns and excluding employee purchases. Our Sales master measure might look like this:
Note that we had to repeat the same set inside each aggregation. We can simplify the expression by using outer set syntax and defining the set once.
This does nothing for reusability, but I believe it improves maintainability. The outer parentheses are not strictly necessary in this case, but they do make it clearer that the set applies to the entire expression.
Continuing on we define a COGS measure:
Note that we could have used the outer set syntax, but I don’t see any advantage to that here.
With the ability to use Measure names in expressions, available in Feb 21 release, let’s define a Margin measure.
Now we can drag the Sales and Margin measure into a KPI.
Now we want to do Sales and Margin for the current year only. Our data requires that we inject a new set. This is where outer sets really shine. The Current Year Sales measure using an outer set:
The Current Year Margin measure:
Note that we have maintained our linkage to the Sales and Margin master measures!
In review, here are all five Measures taking advantage of the outer sets syntax.
I’m happy to see outer sets available in the product. I look forward to the day when sets may be master items!