Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Re: AND in set analysis

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)

chauhans85
Esteemed Contributor

Re: AND in set analysis

comma between two expression worked as and

for example

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



or is like below

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


ashwanin
Valued Contributor

Re: AND in set analysis

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)

MVP
MVP

Re: AND in set analysis

Hi,

Try like this in expression

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

Regards,

Jagan.

Not applicable

Re: AND in set analysis

hi,

thanks for your answer but this is not working.

thornofcrowns
Valued Contributor II

Re: AND in set analysis

Not applicable

Re: AND in set analysis

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

Re: AND in set analysis

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

MVP
MVP

Re: AND in set analysis

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?

Community Browser