topic Re: AND in set analysis in QlikView

AND in set analysis

Tue, 08 Jul 2014 08:04:01 GMT

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

Re: AND in set analysis

MK_QSL — Tue, 08 Jul 2014 08:07:48 GMT

Can you provide little sample data with required output?

EDIT::

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

Re: AND in set analysis

SunilChauhan — Tue, 08 Jul 2014 08:26:40 GMT

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

ashwanin — Tue, 08 Jul 2014 08:26:42 GMT

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

jagan — Tue, 08 Jul 2014 08:37:07 GMT

Hi,

Try like this in expression

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

Regards,

Jagan.

Re: AND in set analysis

Tue, 08 Jul 2014 08:37:22 GMT

hi,

thanks for your answer but this is not working.

Re: AND in set analysis

ThornOfCrowns — Tue, 08 Jul 2014 08:37:42 GMT

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

Re: AND in set analysis

Tue, 08 Jul 2014 08:38:37 GMT

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

MK_QSL — Tue, 08 Jul 2014 08:41:12 GMT

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

Re: AND in set analysis

tresB — Tue, 08 Jul 2014 08:48:02 GMT

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

SunilChauhan — Tue, 08 Jul 2014 09:01:19 GMT

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

Tue, 08 Jul 2014 09:02:17 GMT

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

Tue, 08 Jul 2014 09:08:38 GMT

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

jonathandienst — Tue, 08 Jul 2014 09:11:19 GMT

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

tresB — Tue, 08 Jul 2014 09:12:10 GMT

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

Re: AND in set analysis

petter — Sun, 20 Jul 2014 08:07:47 GMT

With SET ANALYSIS this could be calculated like this:

Sum({<Company=P({<Product={'A'}>})>*<Company=P({<Product={'B'}>}),Product={'A','B'}>} numberOfLicenses)

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

Sum({<Company=P({<Product={'A'}>})>*<Company=P({<Product={'B'}>})>} numberOfLicenses)

(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

petter — Sun, 20 Jul 2014 08:11:41 GMT

Re: AND in set analysis

petter — Mon, 21 Jul 2014 08:27:36 GMT

Actually the expression could be simplified somewhat to this:

Sum({<Company=P({<Product={'A'}>})*P({<Product={'B'}>}),Product={'A','B'}>} Licenses)