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? !
