Set analysis - Ignore selection

[#1]

Good day everyone.

I have the following fields: WarehouseCode, QtySold, QtyOnHand, ItemCode.

I have the following expression: (sum({<ItemCode={"F*"} , WarehouseCode={"ZA01"}>} (QtyOnHand))) / (sum({<ItemCode={"F*"}>} (QtySold))/130)

What I want to do is sum all the QtyOnHand for a specifice warehouse (ZA01) and divide it by the QtySold for ALL warehouses (Not only Warehouse ZA01). Note that I only need this for ItemCodes starting with an "F".

Please help me in adjusting my Set analysis in order to accomplish this.

Thanks

[#2]

Hi,

Replace the double quotes with single quotes and here is the updated expression,

= (sum({<ItemCode={'F*'},WarehouseCode={'ZA01'}>} QtyOnHand)) / (sum({<ItemCode={'F*'}>} QtySold)/130)

Cheers,

Haneesh

[#3]

Thanks Haneesh, but this is not working.

I need the calculation to only take the QtyOnHand of ZA01 & divide it by the QtySold of all the warehouses together (including ZA01).

You see ZA01 is a warehouse that only keeps stock, but does not sell stock.

Hope this makes sense

[#4]

To get sales for all warehouses you could do this:

WarehouseCode = {"*"}

or just ignore WarehouseCode in the set by doing this:

WarehouseCode = ,

Try incorportaing one of these into your expression.

[#5]

So, I think this would give you something like:

(SUM( {$< ItemCode = {'F*'}, WarehouseCode = {'ZA01'} >} QtyOnHand) /

SUM( {$< ItemCode = {'F*'}, WarehouseCode = {"*"} >} QtySold)) / 130

I'm not sure whether you are dividing the whole expression by 130 or just the 2nd part so you may need to adjust the parentheses.

[Miguel_Angel_Baeyens · Accepted solution]

Hello Christo,

I may be wrong, but I'd try

(sum({<ItemCode={"F*"} , WarehouseCode={"ZA01"}>} (QtyOnHand))) / (sum({<ItemCode={"F*"}>} TOTAL (QtySold))/130)

specifying TOTAL meaning all QtySold wher ItemCode starts by F regardless the warehouse.

[#7]

can you try the following:

<Code>

= (sum({<ItemCode={'F*'},WarehouseCode={'ZA01'}>} QtyOnHand)) / (sum({<ItemCode={'F*'},WarehouseCode=>} QtySold)/130)

</Code>

[#8]

Thanks everyone for the help.

Miguel - Your code worked perfect. I tried it just as it is and it worked.

Thanks once again.