Simplifying Set Analysis

Mon, 23 Nov 2015 10:38:07 GMT

In this webinar we will discuss:

Advantages of Set Analysis
When to avoid it
Understanding the components of set expressions
Creating expressions with practical examples
Using Advanced Search with Set Analysis

For best viewing, we recommend you set the highest video resolution.

Q&A - CST Simplifying Set Analysis

Q: Basic idea how Set Analysis works in the backend compared to If condition

A: Here’s snippet from Qlik Community posted by Henric Cronström: “Set analysis is like a selection, that is evaluated before the chart is built.” I think of Set Analysis as just another set of selections behind the scenes. If statements are evaluated row by row in the underlying data, and there can be substantial overhead associated with this.

Q: I never understood the use of the selections from forward and back buttons, could you give me an example please?

A: I certainly felt the same way for some time (where would you ever use this?!) I recently saw a neat, practical example where a sheet was designed to work as a (one way) drill-down/drill-up analysis. Each step in the process could be compared to the prior, sort of like a “what if” analysis. Just remember that triggers can cause trouble with using the forward and back stack.

Q: Hi, Is there a reason why we cannot use fieldName={1} where fieldname has a dual value. I always need to do this ={">=1"} where there is no other value higher than 1. Example: if(Running='t', Dual('Yes', 1), Dual('No', 0)) as Running,

A: Yes – Set Analysis expects the textual value of a dual value field if it is used as a literal value. But using the search string takes care of this for you. This post is regarding dates, but may offer some additional insight: https://community.qlik.com/blogs/qlikviewdesignblog/2015/09/28/dates-in-set-analysis

Q: When using a pivot table, the dimension calculates before the set analysis. If you want to show two columns: sales and previous year sales it wont work if you have month or year as a dimension. Is there any workaround?

A: I think this exemplifies that set expressions are only evaluated once for the entire chart (think of it as an alternative set of selections that will be used for the expression for the entire object.) Each row does not get its own set. I would check out Qlik Community for some possible alternatives; one I have used in the past is a “previous year” field in your data model, and in a set expression replace the selections in the year field with those possible in the “previous year” field.

Q: Is there an equivalent to using isNull(FIELD)? in set analysis

A: Not directly, just like you can’t select a NULL value. You can possibly replace nulls in your data with some value. Check out the NullAsValue statement in the help.

Q: can u explain row by row concept

A: I’ll reference the same answer from a different question: Here’s snippet from Qlik Community posted by Henric Cronström: “Set analysis is like a selection, that is evaluated before the chart is built.” I think of Set Analysis as just another set of selections behind the scenes. If statements are evaluated row by row in the underlying data, and there can be substantial overhead associated with this.

Q: Are there difference between Sense and View regarding set analysis ?

A: The only differences are where some functionality doesn’t exist in one of the two products; for example, alternate states are not available in Qlik Sense as of today, so these cannot be used.

Q: Is there a really good single book, document or site that thoroughly covers set analysis?

A: I am not aware of an all-inclusive document; the online help would be the closest thing to this. I would start with HIC’s recent design blog articles on Qlik Community: https://community.qlik.com/blogs/qlikviewdesignblog

Q: why there is no set analysis wizard in QlikView 11 ? is there any plan to implement it?

A: I can’t speak as to why there isn’t one, nor am I aware of any plans to implement this directly into the products.

Q: BM with Static value has less Bits as compared to Formula as its got calculation and = expansion with it ?

A: I’m not sure I understand the question; the default behavior in QlikView is that, if a search string returns one and only one value, the literal value will be stored in the bookmark as opposed to the search string. The BC_SearchOnlyOneValue Easter Egg can be used to change this behavior.

Q: does the dynamic search string via a web portal? So can a user log into a hosted dashboard and select that bookmark?

A: This works in QlikView Server as well as QlikView Desktop

Q: are there alternative choices of if condition to improve performance?

A: Set Analysis 😃 Alternatively, “flag” fields (with a value of 0 or 1) can be used and multiply by these in your expression. Here’s an old Community post that may shed some light: https://community.qlik.com/message/796209

Q: the " issues when will you fix it?

A: The bug in Qlik Sense where bookmark names wouldn’t work as set identifiers was fixed in 2.0.5, which was released last week and now available on the downloads site.

Q: can you say a little more on if vs set? 

Q: In order to present the intersection of bikes and accessories you had to do an AND calculation. How did you do that? Since Qlik normally only does OR calculations. 

A: I used a set operator in my set expression; in this case the intersection operator “*”

Q: Is it possible to make a dollar expansion that is not in a global setting. E.g. if you want to calculate the maximum year, where you take the dimensions of e.g. a chart into account.

A: Not inside of a set expression, as that be expanded before the chart is calculated. The same would apply for a dollar-sign expansion.

Q: Can you provide a good rule of thumb of using Set Analysis vs. the AGGR function?

A: Set Analysis defines the data (record set) over which the expression will be evaluated, while the Aggr() function is for creating nested aggregations, which is kind of like building a straight table equivalent “behind the scenes” which. These are often used together.

Q: sometimes I can achieve the same using an if statement or set analysis, what is better for performance?

A: Generally speaking Set Analysis will perform better than using If() statements inside of aggregation functions

Re: Simplifying Set Analysis

Mon, 23 Nov 2015 18:01:48 GMT

I thought the Venn diagram in your example document was pretty cool.

I built a document similar to some we use for data analysis at work and I defined a multibox for alternate state C and then defined another multibox for alternate state D. Then, I borrowed the text boxes that make up your Venn diagram and put them in there so that someone can make up a set of selections in the two multiboxes and click on the Venn diagram.

Underneath that, I defined a straight table with columns like SLS HISTORY with expressions using the vSetOperatorExpression variable and values like {C+D} or {C*D} or {D}, etc. So, it becomes a multivariate analysis with "selections" that you can't ordinarily do with list boxes.

I also created a small transparent text box under the Venn diagram and used the nested IF logic from your vSetOperatorExpression to build a text description of the current choice, like "Union, everything selected in C plus everything selected in D".

Thanks for the presentation and useful information!

-Tim

Re: Simplifying Set Analysis

Thu, 03 Dec 2015 20:25:05 GMT

Thanks for the feedback Tim and I'm glad you were able to get something useful from it! I figured someone would be able to find a more practical use for this and take it to the next level (although I'd be lying if I didn't say that my colleague and I weren't jumping up and down in excitement when we got it to work 😃

Best,

-Isaiah

topic Simplifying Set Analysis in Techspert Talks

Simplifying Set Analysis

Re: Simplifying Set Analysis

Re: Simplifying Set Analysis