article Set Analysis - Part 2 - Cool Stuff You Can Do (video) in Qlik Sense Documents

Set Analysis - Part 2 - Cool Stuff You Can Do (video)

Michael_Tarallo — Fri, 13 May 2022 21:58:28 GMT

Set analysis offers a way of defining a set (or group) of data values that is different from the normal set defined by the current selections. Watch this video to learn more about the cool stuff you can do with Set Analysis as well as to get a better understanding of its expression syntax and how it can be used.

Previous Video: A Beginners' Introduction to Set Analysis

NOTE: For a more complete and deeper dive of Set Analysis check out this excellent document: Set Analysis: syntaxes, examples

Sample App: (Qlik Sense is .qvf - QlikView is .qvw)

See attached .qvf file for sample Qlik Sense application
If using Qlik Sense Desktop:
- Copy .qvf file to C:\Users\<user profile>\Documents\Qlik\Sense\Apps
Refresh Desktop Hub (F5)
If using Qlik Sense Server
- Import .qvf using the QMC and then access from My Work Stream in hub
For the .qvw - just opened with QlikView Desktop

Set Analysis Expressions:

Conditions (hard-coded lists,searches,wild card,comparison operators, not equal):

Sum({$<CategoryName={'Bath Clothes'}>}Sales)Sum({$<CategoryName={'Bath Clothes','Babywear'}>}Sales)

Sum({$<CategoryName={"*Clothes"}>}Sales)

Sum({$<CategoryName={"*Clothes",'Babywear'}>}Sales)

Sum({$<Year={'2013'}>}Sales)

Sum({$<Year={2013,2014}>}Sales)

Sum({$<Year={"<2014"}>}Sales)

Sum({$<Year-={2014}>}Sales)

Conditions using variables:

SET vDefaultCat = 'Bath Clothes';

Sum({$<CategoryName={$(vDefaultCat)}>}Sales)

$ Expansion - using functions to evaluate comparisons values:

Sum({$<Year={"$(=Year(Today()))"}>}Sales)

Conditions with multiple dimensions:

Sum({$<CategoryName={'Babywear'},Year={2014}>}Sales)

Conditions with measures:

Sum({<[Model Variation]={"=SUM(PartSales) >=1000"}>}PartSales)

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

josephinetedesc — Sun, 29 Mar 2015 23:41:09 GMT

Hi Michael - a really dumb question - but I cannot open the Sales Analysis with Master Calendar ... what am I doing wrong?
apparently I am using an older version of QlkView 11.20.

Apart from upgrading ... what should I do next?

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

Michael_Tarallo — Mon, 30 Mar 2015 00:03:49 GMT

Hi Josephine - I am happy to hear form you, no dumb questions here . The attached example is for use with Qlik Sense not QlikView - my fault, I should have stated that more clearly. I did attach the data files if you wanted to recreate it in QlikView. I will create a .QVW and ALSO attach it to this post, so we have both for Qlik Sense and QlikView. Let me know if you have any questions, and please ask more questions in the forums and myself and others will do our best to support you.

Regards,

Mike T

Qlik

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

Michael_Tarallo — Mon, 30 Mar 2015 00:17:07 GMT

Hi Josephine - I attached a .qvw for you too - stay well.

Mike T

Qlik

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

josephinetedesc — Mon, 30 Mar 2015 00:19:44 GMT

Thank you

I really appreciated your tutorial – I was going crazy. So now I will have a happy easter ☺

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

Thu, 02 Apr 2015 06:19:02 GMT

I want sample Data modal how create and synthetic key , some data tables and examples.

Please Help Me

Thanks,

sreenu

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

Michael_Tarallo — Thu, 02 Apr 2015 11:26:17 GMT

Hello Sreenu - all of this can be found in this document (attached to the post) and are available from varius videos and tutorials here: New to Qlik Sense Videos

Hope this helps

Mike

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

azimabadi — Wed, 08 Apr 2015 07:14:59 GMT

Hi Michael,

a very good video! I think that Set Analysis is one of the most powerful tools in QV and knowing how to handle it is a grate advantage for every QV-Expert.

Sometimes i have problem with this syntax : {<MyField-={XXX}>} (not always)

That dosen't works simply. Instead i use the following syntax :

{<MyField={*}-{XXX}>}

This works always

Best Regards

Peyman

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

Michael_Tarallo — Wed, 08 Apr 2015 11:44:48 GMT

Hi Peyman - thanks for your comment. Thanks for the tip. We love hearing from our users and seeing how they use the products.

Mike Tarallo

Qlik

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

josephinetedesc — Wed, 08 Apr 2015 22:24:50 GMT

Hi Michael

I made a comment yesterday but could not add a file, so I will make it again (hope there are not 2 copies and I apologise in advance if there are:

I think I have figured it out:

I did it by trial and error though. Is there a better way of doing this?

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

rwunderlich — Wed, 08 Apr 2015 22:57:30 GMT

Hi Peyman,

if you want NOT XXX, the syntax is:

{<MyField=-{XXX}>}

Note the position of the - to the right of =. That selects everything except XXX.

To the left of the = as you posted means "remove XXX from the set".

{<MyField-={XXX}>}

Your workaround you posted:

{<MyField={*}-{XXX}>}

reads as "select everything, then remove XXX". This is logically equivalent to the first =- example above.

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

azimabadi — Thu, 09 Apr 2015 11:11:57 GMT

Hi Rob,

Thank you so much for tip!

That was a misunderstanding from me! I didn't notice the position of "-".

Best Regards

Peyman

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

Michael_Tarallo — Thu, 09 Apr 2015 12:00:04 GMT

Hi Josephine - as per my other comment to you - post all discussion here: New to Qlik Sense - and then you can attach files, samples etc.

Let us know how else we can assist.

Mike T

Qlik

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

Fri, 19 Jun 2015 15:54:39 GMT

Thanks Michael, great video!

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

carahyba — Tue, 08 Sep 2015 21:36:24 GMT

Hi, Michael!

In my data I do not have Year dimension, so I create a master dimension with the following expression: =Year(OrderDate). Works fine, sure. But when comes to define the mesure expression like Sum({$<Year={'2013'}>}Sales), it didn't work. I need to change it for Sum({$<OrderDate={"*2013"}>}Sales) as workaround. Can you explain me why?

Thanks for all videos, I'm learning a lot!

Marcelo Carahyba

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

Michael_Tarallo — Wed, 09 Sep 2015 16:35:29 GMT

Hi Marcelo - Unfortunately at this moment, dimensions / measures defined in the master library cannot be used within the set analysis expressions or other expressions. What you can do is define the new dervied field as using an expression as part of the original load script using the same expression - YEAR(ORDERDATE) as Year - for example - and then Year would work in the Set Analysis expressions.

Glad to see you are learning - stay tuned for more!

Hope this helps

Mike

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

josephinetedesc — Fri, 11 Sep 2015 02:32:50 GMT

Hi Michael et al

I am going crazy trying to see how QlikView is treating my numbers. I am wondering if the answer is set analysis. I thought I had solved my problem using the function Aggr - however - it works on an exmaple I made up but not in real life. Sorry not sure how to get the info here ...

rawQCLDate - rawCTDate = 8.8 (correct) (THIS WILL NOT TOTAL in QLIKVIEW)

sum({$}(rawQCLDate - rawCT)) = 77 (not even sure where that number is coming from

aggr(sum((rawQCLDate - rawCT)),UtilClinic) - gives me the same answer

UtilClinic	UtilPatName	URN	CT	QCL	Start Date	Count({$}[UtilPatName])	QCL - CT	START - QCL	rawQCLDate - rawCT
Neuro,	namex	154100216	28/05/2015 11:30	10/06/2015 11:22	16/06/2015 0:00	1.0	13.0	5.5	13.0
Neuro,	namey	151103763	9/06/2015 15:30	29/06/2015 10:40	30/06/2015 0:00	1.0	19.8	0.6	19.8
Neuro,	namez	94100386	1/06/2015 13:00	10/06/2015 9:00	11/06/2015 0:00	1.0	70.7	0.6	8.8
				Total	3.0	103.5	-	-
WHAT THE ANSWER SHOULD BE			3.0	103.5	6.7	41.6

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

carahyba — Fri, 11 Sep 2015 03:29:31 GMT

Thanks, Michael! It was my final workaround yesterday, put it in the load script. Great!

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

josephinetedesc — Fri, 11 Sep 2015 12:04:53 GMT

I think i have made a mistake in how i wrtoe the agg function !

Re: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

Tue, 02 Feb 2016 13:24:42 GMT

Thx Michael,

Your presentations are fantastic, it made my day. Keep up the good work.

Thanks again

Theo