Days Of Sales Report Help Needed

[#1]

Hi guys

I have developed a days of sales report using SAP Business One data. But I have trouble with one major thing. First off all, let me explain

how my calc works.

I took ItemCode,WhsCode,Quantity,DocDate(where DocDate > Today() -182) from my Invoice table (INV1)

I only took the quantites sold for the last 182 days.

I concatenated it with ItemCode,WhsCode,OnHand from my Warehouse Table (OITW)

I now have on table called "INV+OITW".

Days Of Sales=

sum(OnHand)/(sum(Quantity)/130) (130=No Of Working Days)

This then gives my the number of days of sales of stock I have in my warehouses.

The problem is that one of my warehouses (National Warehouse) does not sell stock, so obviously my DOS calc is not accurate for that Warehouse.

I need to take the sales quantites of all the warehouses together and use it in the calc for this National warehouse.

This National Warehouse calc will look like this =Onhand(only this warehouse)/Quantity(qty of all warehouses)

All the other warehouses will use their own Sales qty's and Onhand qty's except for this one.

Hope someone will be able to help me.

Attached is a table viewer incase you need it.

Thanks