Qlik Community

Ask a Question

QlikView Documents

QlikView documentation and resources.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW

Dynamic intersection of sets (Set Analysis)

Partner
Partner

Dynamic intersection of sets (Set Analysis)

I've seen quite some times, people asking for intersection for some sort of behaviour that would enable them to make selections and based on them, to see the values (a dimension example could be product type) that are mutual for all the selections made.

As examples, we would have this kind of post in the community:

Set analysis permits this sort of thing, but you would have to explicitly input some dimension value, as in the bellow code

=count (distinct  {<code = P ({<exam={"1"}>} code ) >*<code = P ({<exam={"2"}>} code ) > } code )


to be able to do the intersection.


I've developed an automatic way to do it, simply by building each set and then concatenating them (can be expanded to more variables).

I understand that with each additional set of the selection, it can be quite consuming to make the calculations.

My example is quite simple, so it takes no time at all to compute what I've built.

Selections made on 'Dim2'  field will show the intersected values for 'Dim1'

Example:

Selecting the values 'X' and 'Y' in 'Dim2'  field, should give on the righmost table, the values that are intersected for X and Y, giving the following image:

Sample.png


Hope it helps someone, it was quite fun to try it out.

Update: just added code so that the expression is made automatically.

Felipe.

Attachments
Comments
Specialist
Specialist

Hi Felipe, any chance this could be shared for Qlik Sense?

Cheers,

Luis

Partner
Partner

Hi luis,

I also added the Sense qvf file in to the post.

Forgot to post some instructions, but the selection for the intersection must be made on the 'Dim2' field, to check which 'Dim1' values have set intersections.

Example:

Selecting the values 'X' and 'Y' in 'Dim2'  field, should give on the righmost table, the values that are intersected for X and Y, giving the following image:

Sample.png

Felipe.

Specialist
Specialist

Legend Felipe,

I'll be studying the work you've done,

Cheers,

Luis

0 Likes
Partner
Partner

Hi Felip,

I downloaded your qvw file and saw the expression, really cool idea.

But it seems like if there is 5 values in Dim2, then I need to add a x5, am i right? It needs a little hardcode, right?

Thanks.

Aiolos

Partner
Partner

Hi Aiolos,

The set analysis expression is based on how many Dim2 fields you have, and so, if you have 10, the expression is created to get all 10.

As an example, I've put 5 and 6 fields on Dim2, with the following result for the expression:

Dim2: 5 fields

sample1.png

Dim2: 6 fields

sample2.png

So, as you can see, if I add a new Dim2 value, it should get more expressions out of the constructed set analysis.

Felipe. !

Partner
Partner

Hi Felipe,

Really thanks for you explanation, I got it!

Thanks.

Aiolos

Version history
Revision #:
1 of 1
Last update:
‎2017-10-20 09:50 AM
Updated by: