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

ramzi-manoubi — Mon, 20 Feb 2017 09:50:02 GMT

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...

ramzi-manoubi — Mon, 20 Feb 2017 11:04:08 GMT

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

Is there a solution?

topic Re: Sum, all dates, before max date where... in App Development

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

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