How to exclude one item in selection ?

[#1]

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

[#2]

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

[#3]

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

[#4]

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

[#5]

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

[sudeepkm]

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])