Understanding Set Analysis

[eduardo_dimperio]

Hi everyone,

Someone have a good material to help me to understand Set Analysis?

I was looking this forun and found some codes that i dont understant like:

Count(DISTINCT {$<CommercialSite = {"=$(variable1) > 0 and $(variable2)>0"}>} [CommercialSite])

or

Sum({$<[Fiscal Year]={"$(=Only([Fiscal Year]))"}, [Fiscal Period]>} [Sales])

When i use {}, <>,[], $?

Why put some equation between " " ?

Thank you

[petter]

You should definitely have a look at this:

http://help.qlik.com/en-US/sense/3.1/Content/Videos/Videos-set-analysis-introduction.htm

and this too is a good reference:

http://help.qlik.com/en-US/sense/3.1/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-analysis-...

Furthermore three is a website that helps you put together a Set Analysis:

Set Analysis Wizard for QlikView‌ (it is equally valid for Qlik Sense as QlikView and Sense share the same engine)

A quick explanation of the two expressions:

Count(DISTINCT {$<CommercialSite = {"=$(variable1) > 0 and $(variable2)>0"}>} [CommercialSite])

means: Give me a count of the distinct values in the field CommercialSite based on the rows that are satisfied by the condition  (variable1>0 and variable2>0).

Sum({$<[Fiscal Year]={"$(=Only([Fiscal Year]))"}, [Fiscal Period]=>} [Sales])

means: Give me the sum of Sales for Fiscal Year if only a single Fiscal Year value is selected and use values for that Fiscal Year. Disregard any selections made for Fiscal Period which means that all Fiscal Periods should be part of the sum.

The last expression contains an error since it has [Fiscal Period] without a follwing equals sign that it most probably should have.

[lakshmikandh · Accepted solution]

Check this PDFalso

[eduardo_dimperio]

Hi Peter!

Thank you so much for the help

I was seeing your links and unfortunately 2 of them are not working:

http://help.qlik.com/en-US/sense/3.1/Content/Videos/Videos-set-analysis-introduction.htm

http://help.qlik.com/en-US/sense/3.1/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-analysis-...

502 - Web server received an invalid response while acting as a gateway or proxy server.

About what the expression does, i understand, but if i need to write then i'll be completely lost where to put <> and {}.

The other links opened very well and i already start to study, again thank you

[petter]

Actually if you never got any training or haven't read a good book about Set Analysis then looking at these expressions is a bit too much. It is not easy to explain it in a few words in a short answer. You should look at the simpler examples of Set Analysis and then progress into the more advanced features gradually.

Well to explain the basic syntax with the special symbols:

1. The outer pair of { and } tells Qlik that inside the curly brackets there will be a Set Expression.
2. The pair of < and > tells that you want to modify the standard default set of rows and do some special selections among those. The default set of rows are the current selection state of your application. You have a special symbol for it but it is optional and left out in both the examples you had found.
3. The field name that comes after the < will be used to filter the rows.
4. The = sign does NOT mean that it is a comparison even though many people intuitively would think so. Actually it means IN - as "in the set of". Which is similar to what you can use in a SQL statement (where they don't use = for this but the word IN).
5. After the = sign (meaning IN) you have to have something that returns a set of elements or rows. That's why the curly brackets { and } are used to enclose this portion.
6. You have several choices in what constitutes a set:
   1. Literal list of elements could be like this {2015,2016,2017}
   2. Literal list of elements of text like this: {'Actual','Budget'}
   3. Search which must be enclosed in " double quotes: {"Budget*"}     // Everything that begins with Budget
   4. As anywhere in a Qlik expression you can use variable substitution with $-sign expansion like this:
      {"$(somevariable)"} - this will put the contents of the variable somevariable into the search string so it looks like this before evaluation: {"something"}    - that is if the somevariable contains the string something.
   5. and more ... Qlik has training courses for this and the help.qlik.com do have the documentation if you just go there and search for "Set Analysis"

http://q-on.bi has a very advanced online course for Set Analysis which is highly recommended. But most people would be best off studying Set Analysis and using it too before they take this course.

[eduardo_dimperio]

Lak, this material was just awesome!

I think now i understand.

Thank you

[sunny_talwar]

I think one of the best place to start would be this

A Primer on Set Analysis

And then follow some of the links available in the above blog and go through all of them. Another good one would be this one

Implicit Set Operators

For dates, you can check this out

Dates in Set Analysis

[eduardo_dimperio]

Hey Petter!

I read the lakshmikandh‌‌ material, took half day but i think that now i understand the concept.

And your explanation  corroborated with material, so i'm more confident about set analysis.

Thank you !

[eduardo_dimperio]

Hey Sunny, thank you for always help me friend!

[sunny_talwar]

Your always welcome. I would say any topic that you want to learn about, start with HIC's blog and then you will have a good starting point and then start exploring more and more