17 Replies Latest reply: Jul 21, 2014 4:27 AM by Petter Skjolden

# 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?

Regards,

Carl

• ###### 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)

• ###### Re: AND in set analysis

hi,

• ###### Re: AND in set analysis

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

• ###### 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)

• ###### 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

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?

• ###### Re: AND in set analysis

Have had the same way of thinking, know that one field can't have two values at the same time

Thought that there is perhaps a way to solve this, perhaps if i change my data model, my problem is, that i dont know how ...

• ###### Re: AND in set analysis

Hi

Perhaps the problem is this: Since set analysis is effectively a way of coding selections, whatever you are selecting with set analysis must also be manually selectable. Now for AND selections in a list box, the data needs to be structured in a particular way. Once you have done, this then perhaps one of the proposals above will work.

Search here AND LISTBOX or similar, or check the manual for how to structure your data.

HTH

Jonathan

• ###### Re: AND in set analysis

May be in the fron-end too you can achieve that. Could you explain better about your requirement ?

• ###### Re: AND in set analysis

i dont understand what you reuired

but see below for more clear

sum({<A={222},B={'somvalue'}>} fieldname)  -------------------> it will give you result where a=222 and B='Some value'

or is like below

sum({<A={222,322}>} fieldname)--------------------------------------> if A=222 or A=322 then it sum the value

or you may also try below

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

+

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

• ###### 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)

• ###### Re: AND in set analysis

Using a comma separation [A]={'111','222'} will make an OR rather than an AND, I think. Using [A]={'111'}, [A]={'222'} forces an AND.

• ###### Re: AND in set analysis

Hi,

Try like this in expression

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

Regards,

Jagan.

• ###### Re: AND in set analysis

Hi,

Use the union between the two.

Operator    Operator name                                             desc

+                Union                         Returns a set of records that belongs to union of sets.

• ###### Re: AND in set analysis

With SET ANALYSIS this could be calculated like this:

(this will only be calculated for companies with transactions of both product A and B and it will only sum licenses

of A or B).

Or

(this will only be calculated for companies with transactions of both product A and B and it will sum all kinds of licenses for these companies).

Note that the asterisk in a SET ANALYSIS is the set operator INTERSECTION.. So basically by using the
P() element function which selects all companies that have transactions for product A and intersect that with companies that have transactions for product B you will get only companies that have transactions with both products. In addition when you do the sum make sure to specify that you only want sums for these two products... if that is what you need in your chart....

I will also upload a complete example QVW to illustrate even better....

• ###### Re: Re: AND in set analysis

!

• ###### Re: AND in set analysis

Actually the expression could be simplified somewhat to this: