topic Re: Exclude selection listbox. in QlikView

Exclude selection listbox.

jacintof — Wed, 14 Sep 2016 11:30:45 GMT

Hi everyone,

I send a question to see if anyone can help me...

I have the following data model:

FILE_ORDERS:

LOAD:

[Id ORDER],

[Customer],

[NIF]

[Sales]

FROM .......

and:

Table_Blocked_Customers:

Load Distinct * inline

[

NIF| Customer_bloked

XXXXXXXX| CUSTOMER1

XXYYXXYY| CUSTOMER5

](delimiter is '|');

In my QView report I have diferents charts with different calculations. What I want is to have a listbox with blocked customers (listbox with field associated "Customer_bloked") and when I select one/two or alls customers in the listbox then exclude from the calculations (in the charts that I said above) this customers. I've explained ??

One example:

Customer 1 Sales:5

Customer 2 Sales:3

Customer 3 Sales:2

Customer 5 Sales:4

Customer 6 Sales:8

if I don't select any blocked customers, f.ex. the sum of Sales will be 5+3+2+4+8= 22. But if I select in the listbox an blocked customer, f.ex. Customer5, the sum should be: 5+3+2+8=18.

Thanks in advance.

Re: Exclude selection listbox.

sunny_talwar — Wed, 14 Sep 2016 16:54:26 GMT

May be an expression like this:

Sum({<Customer = e(Customer_bloked)>}Sales)

Re: Exclude selection listbox.

jacintof — Thu, 15 Sep 2016 16:19:39 GMT

Thanks Sunny for your answer, but it doesn't work.

I've a separate table (Table_Blocked_Customers) with two fields: NIF field that link with NIF in orders table and customer bloked wich it's the friendly name of customer and the field that use in the listbox.

I've shared an example file...

Re: Exclude selection listbox.

krishnacbe — Thu, 15 Sep 2016 16:33:34 GMT

Try Below expression.

if(GetSelectedCount(Customer_bloked)>0,
sum({<Customer_bloked = e(Customer_bloked)>}Sales),sum(Sales))

Re: Exclude selection listbox.

sunny_talwar — Thu, 15 Sep 2016 21:49:28 GMT

In addition, you don't really need NIF field to link the two tables, it should work without duplicate NIF field

Re: Exclude selection listbox.

jacintof — Fri, 16 Sep 2016 08:10:38 GMT

Hi Krishnapriya,

your expression proposed ins't working quite right.

F.ex. when i select Customer_bloked= COCA-COLA, the result should be:

Abr= 200 May=500 Jun=600 Jul=600 Ago=100

and the result we get, is:

F.ex. when i select Customer_bloked= PEPSI, the result should be:

Abr= 400 May=500 Jun=580 Jul=850 Ago=100

and the result we get, is:

and I select Customer_bloked= COCA-COLA & PEPSI, the result is "Not date to display".

Any suggestions ?

Thx.

Re: Exclude selection listbox.

krishnacbe — Fri, 16 Sep 2016 16:19:22 GMT

Hi,

The issue is due to the null mappings. I have fixed the script and attached.

Hope this helps.

Re: Exclude selection listbox.

jacintof — Sat, 17 Sep 2016 17:46:56 GMT

Hi Krishnapriya,

yes, this would be a solution !!

But finally I made another solution: On general tab of the list box properties,I've selected <expression> from the field list and enter the next expression:

=if(match(Customer,'XXX'),'COCA-COLA',
if(match(Customer,'AAA'),'PEPSI'))

With this solution I deleted the table blocked_customers.

Very grateful for your interest and support.