Data Preparation

[ayadav8]

Hello,

My file contains various product numbers and each product have monthly data(months are in ascending order). I am trying to calculate Central Moving Average of the value. I was able to calculate the CMA but I want the top 6 months (i.e in example 2014-07-01 to 2014-12-01) and bottom 6( i.e in example 2017-06-01 to 2017-11-01) values to be blank.

I have monthly data from 2014 to 2017. The months will always be in ascending order but the start and end months could vary. The selection of top & bottom 6 months should be grouped by Product no.

Also attaching an excel example for clarity.

Any help is appreciated. Thanks!

Product No	Month	CMA
123332	2014-07-01	20
123332	2014-08-01	21
123332	2014-09-01	19
.
.
12332	2017-11-01	30
14566	2014-07-01	55
14566	2014-08-01	60
.
.
14566	2017-11-01	71

[jrgo · Accepted solution]

@ayadav8,

Your example in the Excel file and the table you included in this post were throwing me off on what it was exactly you were looking for, but the solution attached will generate the output to match your Excel file.

 

Hope this helps!

Jimmy

[ayadav8]

@jrgo This is exactly what I wanted. Thanks Jimmy