Visokio website     Downloads     Video tutorials     KnowledgeBase  
Subset formula: 7 Day Moving Average calculation - Visokio Forums
Subset formula: 7 Day Moving Average calculation
  • mttwnxn January 30, 2017 1:06PM
    Hi - I was wondering if anyone could help. What would be the best way to calculate a running average over a moving 7 day window? I want to use this to help create a simple forecast to determine, at the current rate, what would be the closing position position be at the end of the current week or month. Essentially answering questions such as: "if my spend over the past 7 days is maintained, how many ad impressions would I have delivered in February 2017"

    My data would be structured simply as below:

    Date | Market | Campaign | Channel | Impressions | Clicks | Spend
    01/01/17 | UK | ABC | ABC | 111 | 111 | 1111
  • 11 Comments
  •     paola January 30, 2017 1:44PM
    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.
  • mttwnxn January 31, 2017 9:24AM
    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
  •     paola January 31, 2017 9:40AM
    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.
  • mttwnxn January 31, 2017 9:55AM
    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.
    Attachments
    Capture.PNG 10K
  •     paola January 31, 2017 10:49AM
    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.
  • mttwnxn January 31, 2017 10:53AM
    Ok - sorry for my naivety I am new to Omniscope.

    Please see attached the Data file
    Attachments
    Omniscope.xlsx 243K
  •     paola January 31, 2017 12:24PM
    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.

    image
  • mttwnxn February 1, 2017 9:17AM
    Thank you - I will give this a try this afternoon
  •     paola February 1, 2017 10:26AM
    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.
  • mttwnxn February 2, 2017 5:59AM
    Paola - do you have an email I can send an IOK to? I'm having some issues setting this up.
  •     paola February 2, 2017 6:01AM
    support@ visokio

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership