Stock Age Simplify

erhardt_dekock — Thu, 04 Feb 2016 08:26:23 GMT

Hi all

I am looking for a way to simplify my expressions for the attached app. The aim of the app is to show how long stock are in warehouse that has not been sold assuming: first in first out (FIFO)

Created 5 variables to hold the dates of 30, 60, 90 and 120 days ago.

30Days = sum({<PoDate ={">=$(=v30day)<$(=minAllDate)"}>} QtyReceived)

60Days = sum({<PoDate ={">=$(=v60day)<$(=v30day )"}>} QtyReceived)

1. The user selects a date [openCloseSohDate] (end of month)

2. SOHNow =SUM(Aggr(sum(stockOnHandUnits),idProduct))

Note: I know it does not need to be aggregated, but anyway

3. 30Days = sum(aggr(If(SOHNow = 0, 0, if(SOHNow<30Days, SOHNow, 30Days)),idProduct))

Note: I have to aggregate this, because the field is dependent on another and the dimension can switch between ProdTitle and Department.

4. 60Days = sum(aggr(if(SOHNow - 30Days <= 0 or 60Days = 0, 0, if(SOHNow-30Days<60Days, SOHNow, 60Days)), idProduct))

Since I cannot use the "column(1)-column(2)" for "SOHNow - 30Days", and have to replace it with the whole expression of SOHNow and 30Days this gets really big, really fast (see expression for 120+Days.

Is there a way to simplify this? !

topic Stock Age Simplify in QlikView

Stock Age Simplify