Hello Experts, My source data looks something like this. Here the divisor would be changing like 2,3,4,5... DateCloseDivisor21-12-2021172.990005222-12-2021175.639999223-12-2021176.279999227-12-2021180.330002228-12-2021179.289993229-12-2021179.380005230-12-2021178.199997231-12-2021177.570007203-01-2022182.009995204-01-2022179.6999972 Based on that we would need to create a new column called moving average in such a way that if the divisor =2 then Moving Avg would be like rows 1, 1+2, 2+3, 3+4 ex: DateCloseMoving AverageDivisor21-12-2021172.990005172.990005222-12-2021175.639999260.8100045223-12-2021176.279999263.7799985227-12-2021180.330002266.445228-12-2021179.289993269.9749985229-12-2021179.380005268.9799955230-12-2021178.199997268.4800035231-12-2021177.570007266.9850005203-01-2022182.009995268.5750045204-01-2022179.699997271.85999352 we would need to create a dynamic solution of moving average based on divisor. Can someone share an idea?

Dynamic moving average calculation

Hello Experts,

My source data looks something like this. Here the divisor would be changing like 2,3,4,5...

Date	Close	Divisor
21-12-2021	172.990005	2
22-12-2021	175.639999	2
23-12-2021	176.279999	2
27-12-2021	180.330002	2
28-12-2021	179.289993	2
29-12-2021	179.380005	2
30-12-2021	178.199997	2
31-12-2021	177.570007	2
03-01-2022	182.009995	2
04-01-2022	179.699997	2

Based on that we would need to create a new column called moving average in such a way that if the divisor =2 then Moving Avg would be like rows 1, 1+2, 2+3, 3+4

ex:

Date	Close	Moving Average	Divisor
21-12-2021	172.990005	172.990005	2
22-12-2021	175.639999	260.8100045	2
23-12-2021	176.279999	263.7799985	2
27-12-2021	180.330002	266.445	2
28-12-2021	179.289993	269.9749985	2
29-12-2021	179.380005	268.9799955	2
30-12-2021	178.199997	268.4800035	2
31-12-2021	177.570007	266.9850005	2
03-01-2022	182.009995	268.5750045	2
04-01-2022	179.699997	271.8599935	2

we would need to create a dynamic solution of moving average based on divisor. Can someone share an idea?

Workflow

Parse

Transformation

Preparation

Expression

Accepted answers

Jishnu_K

Hi Jaimon,

I have created a Batch macro for this logic. Please find the attachment.

JM_WF.yxzp

All comments

mmenth

Hi @jaimonsk,

I'm not sure how you got those moving number averages, but going by your other instructions I would suggest using a batch macro like the attached. It basically goes through each date in a loop and uses sorting, filtering, and summarizing to isolate the records used to do a moving sum/average.

Hope this helps!

Best,

mmenth

Dynamic moving average.yxzp

Qiu

@jaimonsk
Can you confirm your moving average?

It doest look like what you described. 😁

Jishnu_K

Hi Jaimon,

I have created a Batch macro for this logic. Please find the attachment.

JM_WF.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703