Visokio website     Downloads     Video tutorials     KnowledgeBase  
Historical moving average - Visokio Forums
Historical moving average
  •     Wayne June 16, 2011 10:55AM
    Hi. Is there an operation or a series of operations that can deliver an average value (in a £Cost field, for instance) representative of a desired bracket of time (3 months in a time series Month field, for example). Please assume the Month values are in proper date format and no duplicates are present (one unique month value per cost value). Thank you.
  • 1 Comment
  •     tjbate June 16, 2011 2:40PM
    Wayne - Yes, if you can Rank all the rows/records in your data (and vertically repeated time series observations over time will always rank naturally by time), then you can refer to prior/past/higher/lower records using subset functions that reference ranked values plus or minus a fixed number of rows. In this case the 3-month arithmetic Formula is:

    IF([Row Rank]< 3,0,

    (SUBSET_SUM([Monthly Values], SUBSET([Row Rank],([Row Rank]-2)))
    +
    SUBSET_SUM([Monthly Values], SUBSET([Row Rank],([Row Rank]-1)))
    +
    SUBSET_SUM([Monthly Values], SUBSET([Row Rank],([Row Rank]-0)))

    )/3

    )

    I attach an example file. Note that you do not need this formula explicitly in the file data set to graph moving averages, the Graph View has this option built-in, as shown in the example file.

Welcome!

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

Sign In Apply for Membership