topic Re: How to select sales order containing selected item code from a listbox (in AND logic) in App Development

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

agigliotti — Fri, 30 Sep 2022 12:51:08 GMT

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

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

deepanshuSh — Wed, 28 Sep 2022 16:19:10 GMT

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)

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

agigliotti — Wed, 28 Sep 2022 16:52:46 GMT

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

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

vinieme12 — Mon, 03 Oct 2022 01:47:18 GMT

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

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

agigliotti — Mon, 03 Oct 2022 10:01:14 GMT

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

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

vinieme12 — Thu, 06 Oct 2022 08:57:06 GMT

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)

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

agigliotti — Thu, 06 Oct 2022 10:09:21 GMT

Hi @vinieme12

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

Best Regards

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

RsQK — Thu, 06 Oct 2022 12:29:32 GMT

Hi, this should work:

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

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

agigliotti — Thu, 06 Oct 2022 14:08:26 GMT

Hi @RsQK ,

It does not works the syntax is incorrect.

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

RsQK — Thu, 06 Oct 2022 14:17:19 GMT

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:

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

agigliotti — Thu, 06 Oct 2022 14:45:23 GMT

It still doesn't works, errors in expression.

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

RsQK — Thu, 06 Oct 2022 15:06:48 GMT

Are you using Sense, if so which version? I did this on Aug2022 version. I literally copied and pasted the formula which i posted earlier - it should work. I will attach the .qvf here.

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

agigliotti — Thu, 06 Oct 2022 15:44:45 GMT

Hi @RsQK ,

I was trying in the latest version of QlikView v12.70 and it give me expression error.

I just tried with Qlik Sense and It works as expected.

Great Job!

Many thanks for your collaboration.

Best Regards

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

agigliotti — Fri, 07 Oct 2022 15:14:28 GMT

I tried again in QlikView and the expression does works even if the expression editor gives an error!
It makes sense because QIX engine is the same.

Best Regards