Qlik Community

Qlik Sense Documents

Qlik Sense documentation and resources.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW

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

Michael_Tarallo
Employee
Employee

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

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)

Attachments
Comments
azimabadi
Creator III
Creator III

Hi Rob,

Thank you so much for tip!

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

Best Regards

Peyman

0 Likes
Michael_Tarallo
Employee
Employee

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

0 Likes
Not applicable

Thanks Michael, great video!

0 Likes
carahyba
Partner
Partner

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

0 Likes
Michael_Tarallo
Employee
Employee

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

josephinetedesc
Creator III
Creator III

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 ...

Jo

 

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

UtilClinicUtilPatNameURNCTQCLStart DateCount({$}[UtilPatName])QCL - CTSTART - QCLrawQCLDate - rawCT
Neuro,namex15410021628/05/2015 11:3010/06/2015 11:2216/06/2015 0:001.013.05.513.0
Neuro,namey1511037639/06/2015 15:3029/06/2015 10:4030/06/2015 0:001.019.80.619.8
Neuro,namez941003861/06/2015 13:0010/06/2015 9:0011/06/2015 0:001.070.70.68.8
Total3.0103.5--
WHAT THE ANSWER SHOULD BE3.0103.56.741.6
0 Likes
carahyba
Partner
Partner

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

josephinetedesc
Creator III
Creator III

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

0 Likes
Not applicable

Thx Michael,

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

Thanks again

Theo

juleshartley
Specialist
Specialist

Some nice information and help

Version history
Last update:
‎2015-03-25 03:29 PM
Updated by: