Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
It probably goes wrong with the dimensions in the AGGR. This part: Cust_Id, Facing, Prod_Id
Is there a solution?