Hi, please see a post dealing with similar situation:
http://forums.visokio.com/discussion/2654/formulae-moving-averages-for-daily-datas

If this doesn't help please post a data sample and we will send you the formula.
Also please explain the relationship between the spend (for which we're calculating a moving average) and the impressions.

Hi Poala,

Thank you for the link - it seems applicable. I have multiple values per date - so this doesn't seem to work as the above solution works on the basis of 1 value per date, is this correct?

Thanks

Two records that share the same date will have the same [Rank Date], therefore will be taken into account.

IF([Rank Date]<3, null,
SUBSET_MEAN([Impressions],
SUBSET3
([Campaign Name],[Rank Date],[Rank Date],
[Campaign Name],[Rank Date]-3,[Rank Date],
"=", ">","<="))
)
For all records with [Rank Date] value higher than 2, the above formula will calculate mean for the values in the [Impressions] field, providing a moving average result for every Campaign Name/Date combination, while considering data for the current date and 2 days before it.

Thanks - I think I have a problem with my currentrow() formula. This is assigning a new value to each row, irrespective of date. I am applying currentrow() after first sorting on Date.

Currentrow() is creating a different result for every row, and it cannot substitute Rank.
Please post a data sample csv or iok and we can send you the formula.

Ok - sorry for my naivety I am new to Omniscope.

Please see attached the Data file

Actually this operation will require several steps before it's time to apply the formula. Nice thing about it is that you have to do it only once, and all new data will follow the configured transformation steps, so report updates can be completely automated (or you can do refresh on demand).
In real life campaign might not run on consecutive days, so by separating the fields [Date]/[Campaign ID] and aggregating them, we've made a list of unique values, ready for ranking.
Formula below will create list of consecutive numbers, one rank per Date /Campaign:

RANK([Date], [Date], true, false, SUBSET([Campaign ID]))

After we've merged the list with the original dataset (on 2 fields [Date] and [Campaign ID]), we can apply

IF([Rank Date]<7, null,
SUBSET_SUM([Impressions],
SUBSET3
([Campaign ID],[Rank Date],[Rank Date],
[Campaign ID],[Rank Date]-6,[Rank Date],
"=", ">","<="))
/7
)
Notice how Subset_SUM /7 was used instead of Mean - I guess you wish to add up all the campaign impressions for a 7-day period, then divide by 7.
This will be more appropriate then Subset_Mean, because it will divide the weekly sum by number of rows, rather than by number of days.
File with the workflow is attached, so you can make further adjustments.

Thank you - I will give this a try this afternoon

If Subset formulas are a new concept, please have a look at the file attached.
Click on the Content view with statements, then on the f(x) to reveal the subset formulas behind.

Paola - do you have an email I can send an IOK to? I'm having some issues setting this up.

support@ visokio