count only the visible SKU's in a table with hidden records based on calculated dimensions

[chriscools]

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

[Gysbert_Wassenaar · Accepted solution]

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?

[chriscools]

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

[Gysbert_Wassenaar]

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).

[chriscools]

that's clear, thanx!