Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

AND in set analysis

hello community,

I've got a problem with a set analysis.

I'd like to get all records of companies which use the combination of 2 products, NOT only one of the products.

Example (does not work I know):

1. product:

=sum({<articleNr={111}>} numberOfLicences)

2. product:

=sum({<articleNr={222}>} numberOfLicences)

As information, my data model looks like this:

company as dimension table

calendar as dimension table

products as transaction table

Does anyone know how to solve this?

Thanks in advance!

Regards,

Carl

17 Replies
MK_QSL
MVP
MVP

Can you provide little sample data with required output?

EDIT::

=COUNT({<articleNr = {111}, articleNr = {222}>}DISTINCT numberOfLicences)

Or

=COUNT({<articleNr = {111}, articleNr = {222}>}DISTINCT numberOfLicences)

SunilChauhan
Champion
Champion

comma between two expression worked as and

for example

sum({<A={222},B={'somvalue'}>} fieldname)



or is like below

sum({<A={222,322}>} fieldname)


Sunil Chauhan
ashwanin
Specialist
Specialist

Hi Carl,

I dont' know, wheter i am getting it correct or not. but what i understand is that you required articleNr 111, and 222 with number of licences. If it is so  then use below .

=sum({<articleNr={111,222}>} numberOfLicences)

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this in expression

=Sum(Aggr(If(Count(Distinct articleNr) > 1, Sum(Measure)), Customer))

Regards,

Jagan.

Not applicable
Author

hi,

thanks for your answer but this is not working.

Not applicable
Author

this is not working, if i do it like that, i get all companies which have 222 OR 322, but i Need those which have 222 AND 322

MK_QSL
MVP
MVP

Let me know the edited version working or not...if not working, kindly provide your sample data..

tresesco
MVP
MVP

You are right. But what you are thinking of - is not realistic. At the same time a single field(for you - articleNr) can't have two values.  So AND logic is illogical here. What do you say?