The Magic of Set Analysis – Syntax and Examples

September 11, 2010 at 4:23 pm 17 comments

Update 2014/03/24: We’ve launched an Online Training Center, a place where QlikView users, developers and enthusiasts can share and learn new skills with top-quality QlikView courses. One of the courses is on Set Analysis. If you want to learn more about how to use this powerful functionality, then please look at the course description here and Join us in one of our upcoming live, online sessions!

Visit the QlikOn Learning Center

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:

  1. 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?
  2. 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)
  3. 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.
  4. Yourfinalexpressionshouldlooksimilar to:
    • Sum({$<Region ={‘South’}, Year = {2010}>} Sales)
  5. 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)”}
  6. 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 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.

 

About these ads

Entry filed under: Set Analysis, The Magic Of Set Analysis. Tags: , , , .

The Magic Of Set Analysis. Introduction Reading iPhone’s Database

17 Comments Add your own

  • [...] Part II of The Series, I wrote about the general syntax of a Set Expression and provided some basic examples using Set [...]

    Reply
  • [...] The Magic of Set Analysis – Syntax and Examples [...]

    Reply
  • 3. Taxaw  |  June 25, 2011 at 11:18 am

    Thanks

    Reply
  • 4. DG  |  July 22, 2011 at 5:22 pm

    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

    Reply
    • 5. Mike  |  August 4, 2011 at 11:22 pm

      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

      Reply
  • 6. Qlikview User  |  August 3, 2011 at 7:48 am

    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.

    Reply
    • 7. Mike  |  August 4, 2011 at 11:30 pm

      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

      Reply
  • 8. jub  |  August 17, 2012 at 11:47 pm

    Very crystal clear. Excellent explanation.

    Reply
  • 9. marco  |  December 18, 2012 at 5:03 am

    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.

    Reply
  • 10. Qlikview 11 for Developers « Learn Qlikview  |  August 9, 2013 at 10:33 pm

    […] The Magic of Set Analysis — Syntax and Examples via iQlik […]

    Reply
  • 11. Juho Heino  |  August 28, 2013 at 3:52 am

    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

    Reply
    • 12. Mike  |  August 30, 2013 at 6:19 pm

      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

      Reply
      • 13. Juho Heino  |  September 2, 2013 at 5:37 am

        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 :D

        -Juho

  • 14. Ruchi  |  February 24, 2014 at 11:17 am

    Hi Mike
    Thanks for the very informative post… :)
    Looking to deep dive into more understanding of Set Analysis !

    Reply
    • 15. Mike  |  February 24, 2014 at 11:31 am

      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

      Reply
  • 16. rhona  |  March 4, 2014 at 4:30 am

    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

    Reply
    • 17. Mike  |  March 30, 2014 at 5:40 pm

      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

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,317 other followers

QlikOn!

Join us for one of our upcoming live QlikView courses!

Recent Posts

Twitter

Error: Twitter did not respond. Please wait a few minutes and refresh this page.


Follow

Get every new post delivered to your Inbox.

Join 1,317 other followers

%d bloggers like this: