Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i am trying to establish a counter to show how many articles are in a certain table.
the table was made to show all the articles with orders where there is not enough stock in the shipping warehouse, but there still is in another warehouse.
i basically calculate:
-qty on open salesorders
-qty in shippingwarehouse = Mechelen
-qty in other warehouse = Kontich
-qty missing in the shipping warehous
-qty that can be transferred out of the other warehouse to the shipping warehouse
as dimensions i have:
-SKU
-1 calculated dimension to give me null values if no stock is available in the other warehouse
-1 calculated dimension to give me null values if stock in the shipping warehouse is enough
then i set hide on the dimension with the null values to give me a smaller list
now i am trying to set up the dashboard with KPI for this, that this must be below a certain qty
but what all the expressions and calculated dimensions to get there, i don't have a clue how to count just the articles shown....
can someone help me?
thanx!
i included the file.
grtz,
chris
This is the discount count of SkuCodes that need to be transfered:
=count(distinct if ( aggr ( sum ({< WarehouseName = {'Kontich'}, LocationFunction={'PICKING','BULK'}>} StockQty ), SkuCode, SkuNL)>0
and aggr ( sum ({< WarehouseName = {'WHS BE'}, LocationFunction={'PICKING','BULK'}>} StockQty ) -(sum ({< OrderStatus={"On hold", "Placed"}>} OrderQty) - sum ( {< OrderStatus={"On hold", "Placed"}>}OrderQtyCancelled)- sum ( {< OrderStatus={"On hold", "Placed"}>}OrderQtyDelivered)), SkuCode, SkuNL)<0,
SkuCode)))
Is that what you're looking for?
This is the discount count of SkuCodes that need to be transfered:
=count(distinct if ( aggr ( sum ({< WarehouseName = {'Kontich'}, LocationFunction={'PICKING','BULK'}>} StockQty ), SkuCode, SkuNL)>0
and aggr ( sum ({< WarehouseName = {'WHS BE'}, LocationFunction={'PICKING','BULK'}>} StockQty ) -(sum ({< OrderStatus={"On hold", "Placed"}>} OrderQty) - sum ( {< OrderStatus={"On hold", "Placed"}>}OrderQtyCancelled)- sum ( {< OrderStatus={"On hold", "Placed"}>}OrderQtyDelivered)), SkuCode, SkuNL)<0,
SkuCode)))
Is that what you're looking for?
Hello,
this works !
can you explain perhaps in 1 or 2 words what the way of thinking is to get to this formula?
thanx!
chris
You already had two expressions for the calculated dimensions to filter the records shown in your table: Stock is needed in A, stock is available in B. What I did was put those two expressions inside a count distinct function. Basically it's count(distinct if(cond1) and if(cond2) SkuCode).
that's clear, thanx!