Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
chriscools
Creator II
Creator II

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

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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?


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

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?


talk is cheap, supply exceeds demand

View solution in original post

chriscools
Creator II
Creator II
Author

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


talk is cheap, supply exceeds demand
chriscools
Creator II
Creator II
Author

that's clear, thanx!