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

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

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.

10 Replies
sunny_talwar

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

Gysbert_Wassenaar

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*.


talk is cheap, supply exceeds demand
Not applicable
Author

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

tresesco
MVP
MVP

May be like (not tried though):

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

Not applicable
Author

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)

tresesco
MVP
MVP

Sorry, try :

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

PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

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

Where's the problem??

tresesco
MVP
MVP

Could you post a sample qvw with explained expected output?