topic Re: How to exclude one item in selection ? in QlikView

How to exclude one item in selection ?

Fri, 17 Jan 2014 09:19:36 GMT

Hello,

I'm working on a analysis for Retail business.

We have several Point of Sales (POS). These POS have sales and stock, therefore 1 POS can have SALES and STOCK data.

Regarding the stock, we also have a central warehouse. We treat it as a POS. So it can have stock data but NO sales data.

I'm building a chart to display Sales qty over a selected period and POS. What I want is: if the user select our Warehouse in the POS list ==> display the total sales qty (for all POS).

In the chart expression, i tried:

=sum([Sold Qty])

=> if user select a POS and the wharehouse, it displays the sales for the selected POS: good

=> But if the user select only warehouse, there is no display (=> I want to display sales for all POS)

sum({<[Store Category] -={warehouse}>}[Sold Qty])

=> if user select a POS and the wharehouse, it displays the sales for the selected POS: good

=> But if the user select only warehouse, there is also no display ! or it should be equal to: sum({<[Store Category]=>}[Sold Qty]), right ?

I also tried: sum({<[Store Category] ={*}-{warehouse}>}[Sold Qty]), but it's same as the previous one...

Thank you

Re: How to exclude one item in selection ?

Fri, 17 Jan 2014 10:11:23 GMT

Hi Cyril,

In fact, you need two steps:

1) create a variable that will test the presence of the WareHouse (or just the WH)

sth like (here I test the presence of the ClientID Nr 4 : I want to get ALL in that case):

vTest = if(index(concat(ClientID, ';'), 4)>0, '{<ClientID=>}')

As you see, you get part of the Set Analysis in case of sth

2) Sum($(vtest) Sales)

Fabrice

Re: How to exclude one item in selection ?

Mon, 20 Jan 2014 03:42:03 GMT

Hello Fabrice,

but how can I test the selection in the variable (i'm really beginner in QV), I tried the following but it does not work:

=if(SubStringCount(Getcurrentselections([Store Category]),'WHS')=1,'{<[Store Category]=>}','Getcurrentselections([Store Category])')

Thank you

Re: How to exclude one item in selection ?

Mon, 20 Jan 2014 08:57:12 GMT

Cyril,

Did you try what I proposed ?

Replace ClientID with your dimension, and 4 with your value you want to test.

In fact? i do not know how to answer your question with built-in QV functions. I tried but without success in all cases..Now, I create a string (with concat), I test that the string contains (or not) what I am looking for with index(), or match() functions. If I want the 3d value, I use subfield() function.

Fabrice

Re: How to exclude one item in selection ?

Mon, 20 Jan 2014 10:29:23 GMT

Hello

I Assume the field of the POS list is [Store Category] and the value Warehouse is in it

try this expression

IF([Store Category]='Warehouse', sum({<[Store Category]=>}[Sold Qty]),sum([Sold Qty]),

test it very well if it suits your other filters

Re: How to exclude one item in selection ?

sudeepkm — Mon, 20 Jan 2014 11:08:01 GMT

In your expression can you please try like below. I think you did it but just need to add quotes.

sum({$<[Store Category] ={'*'}-{'warehouse'}>}[Sold Qty])