Sum if greater than

[leenlart]

Hello,

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

but it also returns 0. 

Does anyone have any ideas ?

Thanks!!

[captain89 · Accepted solution]

Hi,

please attach a sample table.

I'm pretty sure that you can do it with set analysis... try this:

create a variable like vDATEJOUR with a variable box.

sum({< CCRI_LOT = {'$DLC'} , VALD_LOTcrit= {"<=$(vDATEJOUR)"}>} MTPXREVSTAT_STD_S_SIT)

[leenlart]

I totally didn't believe that it would work, but it does!!  Thanks!! 

I was using DATEJOUR which is the field with the end date of a month in my calendar.  I don't understand why this didn't work. 

So I created the variable vDATEJOUR, and I set it  = DATEJOUR. 

If you can explain why a variable works better, I would really appreciate it.  It might help me to avoid hitting more walls in the future, as my nose is getting sore. 

[captain89]

You can't compare more than 1 value (VALD_LOTcrit) with another field with more than 1 value (DATEJOUR).

If you use an expression that let you get one value (for example max(DATEJOUR)) you can compare one field with another but i think that a variable is a better way to do that for you in this case.

try without the variable

sum({< CCRI_LOT = {'$DLC'} , VALD_LOTcrit= {"<=$(=max(DATEJOUR))"}>} MTPXREVSTAT_STD_S_SIT)

maybe it runs anyway

Sorry for my english I'm italian. I must improve it.

Thanks for the mark!