Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
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.
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.
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.
can you try the following:
<Code>
= (sum({<ItemCode={'F*'},WarehouseCode={'ZA01'}>} QtyOnHand)) / (sum({<ItemCode={'F*'},WarehouseCode=>} QtySold)/130)
</Code>
Thanks everyone for the help.
Miguel - Your code worked perfect. I tried it just as it is and it worked.
Thanks once again.