topic Set Analysis... Help with this Multiple Condition please! in QlikView

Set Analysis... Help with this Multiple Condition please!

Mon, 20 Jul 2015 15:46:42 GMT

I'va a table with these dimensions:

1)Cliente

2)Descrizione Articolo

I've created two different expressions that calculate sales (vfatturatoAc) and quantity (qfatturatoAC) for each client (cliente) and for specific category of product (N°Conto):

Sales: =Only({<[N° Conto]={"0501001*"}>}VFatturatoAC)

Quantity: =Only({<[N° Conto]={"0501001*"}>}QFatturatoAC)

My question is how can i calculate how many spare parts ([N° Conto]={"0501001*",0501002*"}) has bought those client who bought at least One of this product ([N° Conto]={"0501001*"}).

Hope to be enough clear.

Thanx.

Francesco.

Re: Set Analysis... Help with this Multiple Condition please!

sunny_talwar — Mon, 20 Jul 2015 16:14:46 GMT

Not sure I completely understand, do you have a sample to look at?

Re: Set Analysis... Help with this Multiple Condition please!

Gysbert_Wassenaar — Mon, 20 Jul 2015 16:23:30 GMT

I don't know how you calculate the number of spare parts, but you need a set definition that looks something like:

{<[N° Conto]={"0501001*",0501002*"}, Cliente=p({<[N° Conto]={"0501001*"}>}Cliente) >}

That means filter [N° Conto] to those two values and also select only the possible clients that are left when [N° Conto] is filtered on 0501001*.

Re: Set Analysis... Help with this Multiple Condition please!

Tue, 21 Jul 2015 08:10:57 GMT

Thanx for the answer!

Now, after your advice my expression is:

=Sum({<[N° Conto]={"0502001*","0502002*"}, Cliente=p({<[N° Conto]={"0501001*"}>}Cliente) >}VFatturatoAC)

where:

Spare Parts:[N° Conto]={"0502001*","0502002*"}

Client that bought a specific product: Cliente=p({<[N° Conto]={"0501001*"}>}Cliente

Sales: VfatturatoAc

It seems to work! But....

Now I also have to tell him to exclude customers who bought another specific product [N° Conto]={"0501006*"}. How is possible?

Thanx

Re: Set Analysis... Help with this Multiple Condition please!

tresB — Tue, 21 Jul 2015 08:26:00 GMT

May be like (not tried though):

=Sum({<[N° Conto]={"0502001*","0502002*"}, Cliente=p({<[N° Conto]={"0501001*"} - p({<[N° Conto]={"0501006*"}>}Cliente) >}VFatturatoAC)

Re: Set Analysis... Help with this Multiple Condition please!

Tue, 21 Jul 2015 08:36:15 GMT

it gives me an error in the first bracket:

Error in set modifier expression:

Sum( {<[N° Conto]={"0502001*","0502002*"}, Cliente=p({<[N° Conto]={"0501001*"} - p({<[N° Conto]={"0501006*"}>}Cliente) >}VFatturatoAC)

Re: Set Analysis... Help with this Multiple Condition please!

tresB — Tue, 21 Jul 2015 08:41:11 GMT

Sorry, try :

=Sum({<[N° Conto]={"0502001*","0502002*"}, Cliente=p({<[N° Conto]={"0501001*"}>}) - p({<[N° Conto]={"0501006*"}>}Cliente) >}VFatturatoAC)

Re: Set Analysis... Help with this Multiple Condition please!

PrashantSangle — Tue, 21 Jul 2015 08:58:19 GMT

Hi,

might be i am wrong but i did not get your expression when you say

[N° Conto]={"0501001*"} then it include only that value which start with 0501001 and it will not consider

value which start with 0501006 then why this [N° Conto]={"0501006*"} to exclude it from [N° Conto]={"0501001*"}

???

Regards

Re: Set Analysis... Help with this Multiple Condition please!

Tue, 21 Jul 2015 10:11:31 GMT

I've tried your expression but the results doesn't include only Cliente who bought {"0501001*"}.

Where's the problem??

Re: Set Analysis... Help with this Multiple Condition please!

tresB — Tue, 21 Jul 2015 11:02:28 GMT

Could you post a sample qvw with explained expected output?

Re: Set Analysis... Help with this Multiple Condition please!

Tue, 21 Jul 2015 12:43:43 GMT

Table1: This is the chart with the correct data

Table 2: This happen when i want to see how many spare parts has bought those client that has at least 1 Quantity

As you can see comare some clients that has bought spare parts but has never bought 0501001.

thanx