QlikSense - filter based on omitted items.

[Sdhill]

Hi Guys! if anyone can help, I'd greatly appreciate it.

Preface - I cannot change the script data and the alternate states do not allow me to get to my desired result and also, select excluded/alrernate doesnt work on this type of request

General overview - i would like table 2 to show everything but what is in table 1. 

To explain; 

I have 1 page with;

1x  filter pane [Customer Filter]

1x table [Customer listed]

1x table [Opportunity]

The goal here is to get the filter pane to work on the Customer Listed table and then the Opportunity table should show everything not included in the customer listed.

eg the below includes the customer sales for Customer # 500

Customer Sales
Material	Sold To	SALES _CY	TOTAL SALES
1	A	5	8
2	A	5	7
3	A	5	5
4	A	0	4
5	A	0	13
6	A	5	1
7	A	5	1

In a table beside this will show every line they do not take. Even though their sales for line 1 + 2 are less than the total sales, this is still a line they take therefore needs omitting from the next table.

Oppportunity
Material	Sold To	SALES _CY
4	A	0
5	A	0

material 4 and 5 are the only ones they do not take that are available therefore highlighting an opportunity.

How can i go about this without changing the script?

I can't share the actual data pack either

Thanks in advance 

[Mark_Little]

Hi @Sdhill 

You should be able to achieve this with alternate states, although a little a clunky.

I would be trying something with variables.

vVar  = GETFIELDSELECTIONS(Customer)

The in your second table set expression Customer-={'$(vVar)'}.

You may have to play around to get the Variable to be formatted to get the customer list correctly for the Set Expression. 

[Sdhill]

Hi Mark,

Thanks for replying! i have the below  and tecchnically it works but the data it pulls back isn't accurate - any thoughts? the alternate states doesn't work correctly for this function. or i can't get it to work.

({1<[Sold-To Description]-={$(=GetFieldSelections,([Sold-To Description]))}>}[Sales CY])