I am stuck on a sum problem and I think I need to use AGGR but I'm not very adept at implementing this function yet.
So I have a table : SITUATIONS with the stock situations by date. In this table we have the quantity, value, and state of the Batch number for each item in stock. I have another table CRITERE which has the criteria for each batch number. The criteria are expiration date, sell-by date and use-by date. Each criteria has a VALD_LOTcrit for the date that criteria goes into affect. For exemple the criteria DLC will go into affect the 31/8/2018 for a batch. Each batch does not have an entry in the criteria table for the three criteria we are checking.
I am trying to build a table that will calculate the provision based on the state of the batch and based on the criteria.
To do this step by step, I have added the batch as the dimension, and my expressions are
1. the value * the percentage based on the state,
2. the value if there is a DLC criteria and the VALD_LOTcrit is <= the date selected,
3. the value if there is a DLUO criteria and the VALD_LOTcrit is <= the date selected
4. the value if there is a DLS criteria and the VALD_LOTcrit is <= the date selected
Expression 1 is OK. For the others I seem to have hit a wall.
I have tried this :
if ( CCRI_LOT = '$DLC' and VALD_LOTcrit <= DATEJOUR, sum( MTPXREVSTAT_STD_S_SIT), 0)
but it returns 0.
And I have tried this :
sum( aggr( if (CCRI_LOT = '$DLC' and VALD_LOTcrit <= [DATEJOUR] , MTPXREVSTAT_STD_S_SIT), LOT_SIT))