Solved

Aggregate data based on a condition using a custom window size

  • 21 May 2024
  • 5 replies
  • 178 views
A
Badge

 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'[FILTER : 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,
 

icon

Best answer by Issam Moalla 3 June 2024, 11:42

View original

5 replies

A
Badge

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

Issam Moalla
Rank
Userlevel 5
Badge +9

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'[BY CONSTANT: SHIFT(Month,'Metric 3 Lead Time Periods' + 'Metric 2 Lead Time Weeks'-1)] - 'Cumulated values')
    [FILTER: ISDEFINED('Metric 1 to Aggregate')]

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

     

Hope this helps,
Issam

A
Badge

Hi @Issam Moalla,

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

Garneri

A
Badge

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


Garneri

Issam Moalla
Rank
Userlevel 5
Badge +9

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