Formulae: Moving averages for daily data?

nash · July 23, 2014 3:33AM

My data have KPIs on a daily basis for every day of the week.

I would like to calculate the average of the KPIs for 3, 7, 10, 14 days from the starting date in the data.

Thanks.

paola · July 23, 2014 6:29AM

I assume you wish to create a rolling moving average formula, so for each day calculated value would be mean of values for that day + 2 previous days (for moving average 3).
You need to create an indexing field, and this can be either
RANK(Date,Date,true,false) for ascending order
or
CURRENTROW()
(if you have previously used Sort block to create chronological order)

Aim is to create unique value for each row, as I'm assuming you have only one value per day. Now you can create new formula field (moving average for 3 day periods):


IF([Rank Date]<3, null, 
SUBSET_MEAN([Impressions], 
SUBSET3
([Campaign Name],[Rank Date],[Rank Date],
[Campaign Name],[Rank Date]-3,[Rank Date],
"=", ">","<="))
)

nash · August 5, 2014 10:18AM

Thanks paola, it works. However for the next step, I need to calculate only third value, so they are independent. Can you help me on that?

nash · August 5, 2014 11:06AM

This a data example. I want to generate the forth column in the excel sheet.

paola · August 5, 2014 11:16AM

IF(MOD([ID],3)=0,1,0)

nash · August 6, 2014 2:59AM

This is easy. I wasnt aware of this formula. Thanks a lot!!!

Welcome!

Categories

Ideas Parade

Tagged