Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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])