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.
