1 Reply Latest reply: Feb 20, 2017 6:04 AM by Ramzi Manoubi RSS

    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.