Summary: I demonstrate how outer sets in Qlik expressions can be used to extend the reusability of Master Measures.
The new outer sets feature in Qlik brings new opportunities for Master Measure reuse in Qlik Sense and QlikView. See this post by Henric Cronström for an explanation of the new feature.
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!
-Rob
Thanks Rob, I never knew about that functionality, that will definitely help reduce overall maintenance in a lot of my expressions.
I would still love to see the possibility of adding set analysis to an entire object i.e. an over arching set analysis applied to all expressions in an object, saving having to repeat set analysis for all expressions where there is commonality.
Hi Derek
Chart Filters, currently available in Qlik Sense SaaS – Simplified Authoring, provide thee object level set you are looking for. The current UI doesn’t let you directly edit the set expression, but I hope that will become available in the future, as well as on-prem. See https://qlikviewcookbook.com/2022/07/the-new-qlik-simplified-authoring-mode/
I’m not sure it is a great solution to propose, but you can assign a string value to a master measure like ‘{}’ and then in a measure expression use the “set expression” master item with $(=[Set Analysis Year]) [Sales]. I thought I’d just throw that out there.
Ha. The set expression string example for the master measure [Set Analysis Year] didn’t go through. Anyway, I hope it was enough to understand the idea.
Excellent one!! This improve the resuability
Late to the game on this, but to piggyback off of Karl’s and others’ comments, I have implemented variables in our outer set analysis as follows:
1. I created “generic” master measures (e.g., Sales, Cases, etc.) that are simple aggregations with no set analysis applied (e.g., [Cases] = sum(Cases)).
2. Then I created “set analysis variables” that contain set expressions (e.g., for YTD, we might have the variable fYTD = {}. NOTE: I store that expression as-is–not as a string. The editor says it’s a syntax error but it will work once it’s used in the next steps, so ignore that warning.)
3. Then I created new master measures that tack these variables onto the generic master measures as needed. For instance, for a master measure called [YTD Cases], I have this as its expression: $(fYTD)[Cases].
So here’s the really cool part: Those are modular/stackable! So if I have a variable containing set analysis to identify cases in the US — fUS = {} — I can have a master measure called [Domestic YTD Cases] = $(fUS)$(fYTD)[Cases].
Storing these set expressions in variables does two big things:
1. it allows us to abstract away the complicated set analysis, meaning users can create their own metrics containing set analysis without having to know how to create set analysis. They just have to know the variable name. You can almost think of these set expression variables as “master” set expressions; users now have the flexibility of building their own metrics without having to worry about whether their set analysis is built correctly. They just have to make sure they have included all the right “building blocks”.
2. these variables can be stored in a centralized repository (I have ours in a database) that Qlik can pull in via the data load editor. This allows developers to quickly build apps that might require many of the same measures without having to copy/paste or reinvent the wheel. It also means that if the business logic changes for those measures that show up across multiple apps, you only have to change the logic in the repository and then reload all the apps; the set expression variables will all update, thus so will all your measures in all the apps. To Mohan’s point, the reusability is dramatically improved.
Very nice implementation and explanation. Thanks for sharing.
It should be pointed out, though, that States can be a little… contrary… due to inheritance rules (see Henric’s post mentioned above). It has been my experience that even the default $ state can cause issues when trying to “stack” set analysis components this way, so I leave it out. And certainly the use of alternate states is particularly challenging; I haven’t quite figured the secret sauce to consistently make those work as intended yet. Any advice anyone might have on that would be appreciated!