Weighted Moving Aggregate (but with a twist)

[badger101]

Greetings all I am trying to calculate a weighted moving aggregate but the only other forum thread that is related to this had no detailed info from the inquirer’s side.

Weighted Moving Average

Hi I need to calculated Weighted Moving Average. I am not sure how to do this because there is no option to choose Weighted Moving Average in both Moving aggregation and Moving Average Node.

I have taken a look on the workflow by @aworker on that thread. I would like to start fresh with all the details for my case that are not present in the workflow:

Suppose I have datapoints like in Column A shown here:

Now, I am looking to get Column B. Column C is simply there to show the formula needed to get the values shown in Column B.

As you can see in Column C, the weights need to be reset or recount for each succeeding row. The examples use a lookback period of 3, hence why the first two rows are mostly empty and calculations should start from Row 3 onwards.

For this case, since the lookback period is 3, then the weights start with 1, followed by 2 and 3. Then it recounts/reset. Putting that into perspective, for example, Cell A2 will be multiplied by weight values of either 1 or 2 depending on which iteration/round/rotation it’s on.

Hope I’ve clarified my case clearly. Here’s the dummy data:
Question Forum Weighted Moving Aggregate.xlsx (8.7 KB)

Let me know if you have further questions!

Thanks in advance.

[ArjenEX]

Hi @badger101

It sounds like a job for the access feature:

if (rowIndex() > 2) {
    round((column("value",-2) * 1) + (column("value",-1) * 2) + (column("value") * 3),2)
} else {
    null
}

Result:

The rowIndex() comparison is used to account for the first two rows. The round() is for ease of reading. As always, make sure to properly initialize the access feature in the settings of the CE.

Hope this helps!

[bruno29a]

Hi @badger101 , is the lookback period dynamic? Does this need to be calculated automatically based on the lookback period? Or will you adjust manually for 2, 3, 4, etc?

[badger101]

This works! Very creative and efficient, thanks a lot @ArjenEX !

@bruno29a The lookback period is fixed at 10 (the dummy example used was 3 for simplicity purposes). But feel free to share a solution for a dynamic case, perhaps it would be useful for me in the future and for others. At this point in time, what @ArjenEX did is already sufficient for what I’m looking for.

[aworker]

Hi @badger101

The lookback in the solution you had mentioned from me is dynamic. This is why it is more complex. It is dynamic in terms of the number of weights and their values. One just needs to fill a table with the required number and values of weights and it should work.

I’m not yet in the office but will send soon an example based on your data if this may help.

Best,
Ael

[aworker]

Hi @badger101

Please find below the workflow adapted to your data:

The mask has been set to [1, 2, 3] values in the second table as in your example:

I added a -Rule Engine- node at the end of the workflow to set to Zero the initial row results that have index less than the number of weights in your mask:

The link to the workflow in the HUB is the following:

20230519 Pikairos Weighted Moving Average – pikairos

Hope it helps.

Best,
Ael

[badger101]

Thank you @aworker for taking the time. Yes, based on your explanation and images, I get how it should work for my case now. The Rule Engine trick at the end is the key. Again, thanks for sharing!

[aworker]

Always my pleasure @badger101 !
Best
Ael