Syntax & Examples
In this new Post of The Series, I will go over the details for creating a correct Set Expression. Also, I will provide some useful examples for you to get your Hands On right away.
Follow these steps to build up your set expression and use the correct syntax:
- First of all, you need to define what you want your expression to return. A good method to get it right would be to answer the following questions first:
- What field will I use in my expression? (For example: ‘Sales’, containing the sales amount for every invoice.)
- How will I aggregate the field? (It can be using Sum, Count, Avg, etc.)
- What explicit selections do I need in my expression? Here you define a Field and its value(s). (For example: I want ONLY the ‘South’ Region. Another example would be to only include values associated with certain Year.)
- Do I need to exclude/ignore some selections or values?
- After you’ve answered the questions, you can go on to compse the expression. If you want to Sum the Sales amount, you would start with something like:
- Sum(Sales)
- Then,wheneedtoaddtheSetportion of the expression.:
- This portion goes just after the first parenthesis, before the Field Name. The Set Expression will be enclosed in curly brackets: {set expression}.
- After the first curly bracket we add either a dollar sign (which means the record set will be based on the current selections) or a number 1 (meaning we will use the full record set of all the records in the application). We will use the dollar sign to illustrate, since it is the most common, so you will now have {$}. Important to note is that the dollar sign can be ommited and the set expression will not be affected. It is good practice, however, to use it.
- After the Dollar Sign, we define the fields that will play in our set expressions. All of these field-value definitions will be encolsed in less-than and grater-than symbols (< >). The syntax is FieldName = {FieldValue}. If FieldValue is a literal or text, you should enclose it in single quotes. If you want to use a search string as the FieldValue, enclose it in double quotes. Here are some examples:
- {$<Region = {‘South’}>} will result in a record set taking the current selections ($) where the Field Region has a value of ‘South’.
- {$<Year = {2010}>} will give return a record set based on current selections ($) where the Year is 2010 EVEN if you select something else in the field Year.
- {$<Year = {“20*”}>} will give you the record set based on the current selections where the Year matches the search string “20*”, meaning all years that begin with “20”.
- {$<Year = {“>=2007”}>} Will return a record set where the Year is greater than or equal to 2007. Notice that here we are using a search string.
- {$<Region = {‘South’, ‘North’}, Year = {2010}>} will give you a record set based on the current selections where Region is equal to South or North and Year is equal to 2010.
- Yourfinalexpressionshouldlooksimilar to:
- Sum({$<Region ={‘South’}, Year = {2010}>} Sales)
- You can use variables instead of the hardcoded Field Value:
- If your variable is a number, use it as Field = {$(MyVariable)}
- If your variable is text, use it as Field = {‘$(MyTextVariable)’}
- If your variable should be used as a search string, use Field = {“$(MySearchVariable)”}
- Also, you can create calculations to use them as Field Values:
- Year = {$(=Max(Year))} It is just as if you would use a variable (described above), just with an equal sign. NEVER forget the equal sign here.
So, now you should be able to get up and running creating your set expressions. Remember: This is a basic introduction and there is A LOT more to know about Set Analysis, I will continue with the series to cover some more of it.
Keep Posted. Any comments are welcome!
Update 2019/04/25: If you found this useful, you may want to take a look at the Set Analysis Cheat Sheet and invaluable resource for Qlik Developers.
Update 2012/12/13: Barry Harmsen and I have just released our book QlikView 11 for Developers. While it is not free, it does offer a cost-friendly, guided way to learn QlikView and contains an entire chapter about Set Analysis. More information can be found by clicking here.
[…] Part II of The Series, I wrote about the general syntax of a Set Expression and provided some basic examples using Set […]
[…] The Magic of Set Analysis – Syntax and Examples […]
Thanks
Thanks for this good info. One question:
In part 5 you show how to use a variable.
Could I use a label reference?, i.e. a label reference as is discussed here in a different context:
http://www.qlikfix.com/2011/06/21/testing-the-performance-implications-of-variables-and-label-referencing-versus-direct-expressions/
Thanks
DG
Hi DG,
In the case of Set Analysis, it is not possible to use a label reference as Set Modifier because the “Set” (Data QlikView will use to perform the calculation) is defined only once and applies equally across all the chart dimensions. Using a Label reference would be in an attempt to use different Sets for each row (supposing it is a table), which is not possible.
Thanks for your comment and hope I could help.
Mike
Thank you for sharing. Very clear examples.
You mentioned:
If your variable is text, use it as Field = {‘$(MyTextVariable)’}
If your variable should be used as a search string, use Field = {“$(MySearchVariable)”}
In what scenario do we use search string in set analysis? Can you please provide some examples?
Also, I’ve sen expression ‘$($(=only(variable)))’ (i.e. that is the only line in the expression). Is it set analysis? What does it mean?
Thank you.
Hi!
‘$($(=only(variable)))’ is not a Set expression, don’t get confused by the dollar signs. Dollar signs represent a whole other subject, called “Dollar-Sign Expansion” which basically I like to translate as “evaluate/compute whatever is inside the parentheses (which are preceded by the dollar sign)”.
It is most commonly used to get variables’ data because if your variable is an expression [for instance: Max(Year)], it will return the already-computed value (say, 2011).
This is a pretty basic idea of Dollar-Sign expansion, but it is a topic on its own, with its syntax rules and everything. You may be able to find something already on the Qlik Community about the subject, or on other blogs (Check the blogroll on the sidebar). I will also consider it for future writings here in iQlik.
Thanks for your comment.
Mike
Very crystal clear. Excellent explanation.
Thank’s for this very clear explanation…. it seems the qlik engineer like the confusion.. the same symbol with different uses, the simple and double quotes very significant… not easy before your guide.
[…] The Magic of Set Analysis — Syntax and Examples via iQlik […]
Hi Mike!
I followed your approach from QlikView 11 for Developers and used $(vSetPreviousMonth) as part of set analysis to get data for previous month. If ran now, this would be 07/2013, which matches the MonthYearID 79 used in the set [I replaced PeriodID with MonthYearID].
My question is this; how could I get the matching MonthYear value (07_2013 matching 79) as expression label in my chart? I would like the label to show whichever MonthYear is currently used in the set analysis.
-Juho
Hi Juho,
You could use something like
=MaxString($(vSetPreviousMonth) [YearMonth])
Don’t forget the equals sign at the beginning.
Hope this helps and thanks for reading.
Mike
Hi Mike,
and thanks, but I ended up using the following for the label;
=FieldValue(‘MonthYear’,$(=Max(MonthYearID)-1))
So I just take the MonthYearID-part of the variable in question and fetch the matching MonthYear value from my calendar table. Simple, but works 😀
-Juho
Hi Mike
Thanks for the very informative post… 🙂
Looking to deep dive into more understanding of Set Analysis !
Hi Ruchi,
I’m glad you found this post helpful. If you’re looking to learn more about set analysis, you’ll probably be interested in an online course (specifically about Set Analysis) that my co-author Barry Harmsen and I will be delivering via online. We’ll announce it this week but if you’d like to know more please provide me with your work email address and we’ll send you the deatails of the course once it’s announced.
Cheers.
Mike
Hi I am trying to do a sum of item quantity where the orderitemstatus=4 and the comments field is empty. I have:
sum({}item_quantity)-sum({}item_quantity)
but its not working as it is taking away the sum of all the comments fields with comments where as I need it to take away the comments fields that have comments when the order_item status is also 4
Any Ideas its driving me mad
Thanks
Rhona
Hi Rhona,
As much as I’d like to help, I’m not able to respond to this type of queries via the comment sections on my blog. The information provided here is a tutorial that, if followed, will allow readers to learn about how QlikView works in various aspects. However, it’s not meant as an exhaustive resource and is not expected to cover every single scenario that you may encounter. For this reason, and in case the resources published in this website are not enough for your learning needs, I suggest the following:
– Find help on QlikCommunity
– Read our book QlikView 11 for Developers which has more detail in this topic (http://iqlik.wordpress.com/get-the-book)
– Sign up for an online course on set analysis at http://q-on.bi/courses
Kind regards,
Mike