How to select sales order containing selected item code from a listbox (in AND logic)

[agigliotti]

Hi,
I'd need to select the sales orders that contains the selected item code from a listbox.
Ex. if the user selects item code 100 and 200 from the listbox, then the expected result should be the sales order that contains both item code that is 100 AND 200.
Could someone help me to achieve it?
Many thanks in advance for your time.

Best Regards

@vinieme12 

@sunny_talwar  @hic @stevedark @Gysbert_Wassenaar @rubenmarin @Thiago_Justen_ @Anil_Babu_Samineni @kaushiknsolanki @marcus_sommer @tresesco  

[deepanshuSh]

Hi, you can achieve that using get field selection function and if and else condition. Maybe like this;

if (GetFieldSelections(field1)>1, Sum({<itemcode ={'GetFieldSelections(field1)'}>}Sales) 

[agigliotti]

Hi @deepanshuSh ,
It's not what I'm looking for.
I'd need a table with sales order number as dimension and sales amount as measure.
The sales orders showed must contain all item code selected from the listbox by the user (ex. item code 100 AND item code 200) within its rows.
I hope now is more clear.
Best Regards

[vinieme12]

This is default Qlik behavior , measures will evaluate for selected values

Assuming your data is properly associated, you just need to have a chart as below

Dimension: ItemCode

Measure: sum(Sales)

Can you post a screenshot of your data model?

https://www.youtube.com/watch?v=DuULZtyjrqU

[agigliotti]

Hi @vinieme12 ,

My request was a little different...
I'd need to apply an AND logic for the selected values in order to find only the sales orders with ALL product items selected.
I know the default behavior is with OR logic for the selected items.
example:
order number, product item
100, A
100, B
200, C
200, D
300, B

if the user select product item A and B  I'd expect to see only the order number 100, while Qlik shows also the order number 300.

Best Regards

[vinieme12]

Try below 

All selected

=Sum({<Ordernumber={"=count({<item>}Distinct item)=getselectedcount(item)"}>}Sales)

All selected plus others not selected 

=Sum({<Ordernumber={"=count(Distinct item)=getselectedcount(item)"}>}Sales)

[agigliotti]

Hi @vinieme12 

I need to take care of all specific item code selected not only of their quantity (count distinct item).

Best Regards

[RsQK]

Hi, this should work:

COUNT({<[order number]=$(=CONCAT(DISTINCT 'P({$<[product item]={' &CHR(39)& [product item] &CHR(39)& '}>} [order number])', ' * '))>}DISTINCT [order number])

[agigliotti]

Hi @RsQK ,

It does not works the syntax is incorrect.

[RsQK]

Oops, my bad. With this exact formula:

COUNT({<[order number]=$(=CONCAT(DISTINCT 'P({$<[product item]={' &CHR(39)& [product item] &CHR(39)& '}>})', ' * '))>}DISTINCT [order number])

if i select products A and B, i get a count of 1: