Skip to main content

 Hi Pigment community,

A similar topic has been opened before, the proposed solution was this:
 


However, not all the time I need the same amount of data to aggregate, since my metrics are variable (sometimes I would need to aggregate 10 values, sometimes 1), what its the possible workaround?

Tried using a formula like this: 
CUMULATE('Metric 1 to Aggregate'tFILTER : Month.'Start Date'<=(Month+'Metric 2 Lead Time Weeks').'Start Date' AND (Month+'Metric 2 Lead Time Weeks' + 'Metric 3 Lead Time Periods'-1).'Start Date'>= Month.'Start Date'],Month,"".SUM)

Trying to isolate only the months I want to aggregate, but it doesn’t works.
I tried to make and very detailed post but for whatever reason the topic wasn’t creating.

 

Tell me if you need more details. Thanks in advance,
 

I need to create a Metric dimensioned by Months and Products (Let’s call it “Metric 4”), I want to aggregate another metric that it is also dimensioned by Months and Products but only aggregate it on the desired window determined by other 2 blocks that also has Month and Products

 

The desired behavior would be:
IF we looking at May 22:
May 22 = 0 
May 22 +1 = Jun 22 = 36
May 22 + 2 = Jul 22 = 37
May 22 + 3 = Aug 22 = 38

The final output on May 22 should be = 111 as you can see in the Metric 5 for Desired Output

The main reason that I can’t use MOVINGSUM it’s because that function requires scalar integers as End and Start Periods and removing Month and Product dimensions would cause the main issue to continue

Hi @A_Garneri ,
 

Currently the window size for the moving sum is not variable and need to be scalar.
However, there is a possible workaround to achieve this. In order to get to the final result my solution consists in applying an intermediate metric where I calculate the Cumulated values on the Metric to aggregate.

  • Cumulated value formula:  
    Cumulate('Metric 1 to Aggregate',Month)
  • Then the final metric (Metric 4) would look for the cumulated value based on the offset from Metric 3 Lead Time periods and Metric 2 Lead Time Weeks using the Shift function. and subtract this value from each months cumulated value. The formula would be as follows: 
    ('Cumulated values'uBY CONSTANT: SHIFT(Month,'Metric 3 Lead Time Periods' + 'Metric 2 Lead Time Weeks'-1)] - 'Cumulated values')
    rFILTER: ISDEFINED('Metric 1 to Aggregate')]

    Where The rFILTER: ISDEFINED('Metric 1 to Aggregate')] is applied to only get the results where Metric 1 has values.
     

     

Hope this helps,
Issam

Hi @Issam Moalla,

Really appreciate your answer, this was the solution to my problem. You helped me a lot.
Regards.

Garneri

Just as side note, you should add “Shift” Function and any other that is not listed into this post:


Garneri

Hi @A_Garneri ,

Thank you for the feedback, the SHIFT function has been added to the Functions by Category Post.

Issam

Reply


Ask your question here!

Terms & ConditionsCookie settings