Rolling 3 week average

[avastani]

Has anyone ever done a rolling 3 week average?

I know we can use the RangeSum(above()) type expression but I wanted it to be more dynamic as RangeSum assumes that you have data in a particular way for straight tables, and charts and pivot table might use before and then there is the sort issue.

too many stipulations with the RangeSum and I was looking to use something like

sum

with InWeek function instead but cannot since my data is at weekly level only.

Any ideas? Thanks for your help.

(if(inyear(MonthYear, $(vPeriod), 0, month(addmonths($(vPeriod), -11))), LineSalesAmount))

[#2]

I am having the same problem, I want to graph a monthly line and a 3-month rolling average. Is set analysis the only way to go or is there a nifty function I haven't found?

[avastani]

not really, you can precompute the rolling 3-month sum in the script and divide by 91.25 or 90 for your rolling 3 month average.

essentially you have to create a separate table where you will outer join your fact data and bring the other prior 2 months on the same level as the current month based on a temp join key which is really the current month's date. then add the data and do a group up and divde to get your rolling average monthly.

it is easy when you know the no of months of weeks to average, but i am trying to do the same dynamically whereby the user picks an interval to do the average over.

[johnw]

Or instead of precomputing, just tie one "AsOfMonth" to the matching three actual "Month" values. See attached.

But the attached approach still assumes you know, at script time, how many months you want. Here, it sounds like we want the user to be able to select a number of weeks on the fly, like with a slider? I don't have a clue how to tackle it yet, but it sounds like an interesting problem.

[johnw]

OK, I think I have a good solution. See attached.

There's a linkage calendar that ties an AsOfMonthYear to that MonthYear and every previous MonthYear. That table also has a MonthsBack field that tells you how many months back the data for that MonthYear is from the AsOfMonthYear. Then I use a multi-value slider for MonthsBack. The expression is, in the example:

sum(Revenue)/count(MonthsBack)

Conveniently, this even handles the problem of averaging by the right number of months when the number of months of data is less than the number of months we're trying to average. It automatically recognizes that there are fewer months to average for the first few months, without any complicated expressions. The multi-value slider even lets you specify different periods, such as the rolling year ending six months before the given date. Hopefully I'm not missing any big holes in the idea.

Edit: For people on personal edition who can't load sample files, here's the script:

[Main]:
LOAD *
,date(monthstart(Date),'MMM YYYY') as MonthYear
;
LOAD
date(date#(20080101,'YYYYMMDD')+floor(rand()*1000)) as Date
,ceil(rand()*100) as Revenue
AUTOGENERATE 500
;
[Date Linkage]:
LOAD DISTINCT MonthYear
RESIDENT [Main]
;
LEFT JOIN ([Date Linkage])
LOAD MonthYear as AsOfMonthYear
RESIDENT [Date Linkage]
;
INNER JOIN ([Date Linkage])
LOAD *
,round((AsOfMonthYear-MonthYear)/30.436875) as MonthsBack
RESIDENT [Date Linkage]
WHERE MonthYear <= AsOfMonthYear
;

[#6]

I have almost similar challenge. I should calculate a four weeks average on week level (last four week/4) and get it to a report. Report should look exactly the same as in your example, but I should use weeks instead of months.

Here is your script that I tried to modify. It doesn't create week numbers and it also fails for some reason. Could some have a look at it?

[#7]

Brilliant solution. Transformed it to weeks and it works flawlessly (except with weeks your slider gets waaay longer..) Thank you, John!

[#8]

Thanks John!  This is a very elegant solution, which solved a similar issue for me.

I especially like that this solution works with existing filtering and the MonthsBack slider allows for averaging over as user-defined time period.