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

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

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.

• ###### Re: Sum, all dates, before max date where...

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

Is there a solution?