7 Replies Latest reply: Aug 4, 2010 6:57 AM by Christo Roder

# Set analysis - Ignore selection

#### 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".

Thanks

• ###### Set analysis - Ignore selection

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

• ###### Set analysis - Ignore selection

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

• ###### Set analysis - Ignore selection

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.

• ###### Set analysis - Ignore selection

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.

• ###### Set analysis - Ignore selection

can you try the following:

<Code>

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

</Code>

• ###### Set analysis - Ignore selection

Thanks everyone for the help.

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

Thanks once again.

• ###### Set analysis - Ignore selection

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.