Sum, all dates, before max date where...

[ramzi-manoubi]

Once more, I hope for your help.

Context: CRM, registering the number of products on the shelf in the stores. I use a treemap to vizualise this, very pretty.

I use this variable to filter, so all data before the max date in the filter would be taken into account. Very standard I think.

vDateBeforeMax

{$<Year=,Quarter=,Month=,Day=,Date={"<=$(=max(Date))"}>}

I want to take the sum of the most recent facings, before the max date in the filter. To get the most recent registration, I use Last_Reg. The smaller the number, the more recent the registration. This explains following code:

    if(    Min($(=vDateBeforeMax)    Last_Reg)    =     Min($(=vDateBeforeMax) total    <Prod_Id,Cust_Id>    Last_Reg), 

      Facing

     )

I tested this structure in other formulas and it works.

This is the complete formula:

Sum($(=vDateBeforeMax)

  Aggr($(=vDateBeforeMax)

    if(    Min($(=vDateBeforeMax)    Last_Reg)    =     Min($(=vDateBeforeMax) total    <Prod_Id,Cust_Id>    Last_Reg), 

      Facing

     ),

     Cust_Id, Facing, Prod_Id

    )

)

Where it goes wrong: When I filter on 2017 I only get for a certain product 6 facings. When I also add 2016 in the filter I have >200 facings.

Having only 2017 in the filter, should already take in account the data for 2016 and all previous years.

[ramzi-manoubi]

It probably goes wrong with the dimensions in the AGGR. This part: Cust_Id, Facing, Prod_Id

Is there a solution?