Saltar al contenido principal

Comparing a Rolling Average Period-over-Period DataFlow

Version 2


Important: When referencing this page outside of Knowledge Base, use this link:

At times you may want to create a card with, for example, a three-month moving average for this year compared to the same period last year, to see if they are trending up or down in comparison to the previous year.

To create the DataFlow for this calculation, create a transform like:

      SUM(`ValueField`) / COUNT(*)  
      TABLE t2
      t2.`DateField` > LAST_DAY(DATE_SUB(t1.`DateField`, INTERVAL 3 MONTH))
      AND t2.`DateField` < LAST_DAY(t1.`DateField`) ) AS `Rolling_Average`

  TABLE t1

This transform assumes that the "DateField" column contains a date representing the month, e.g. 2015-01-01 for January.

The subquery retrieves the sum of the last 3 months’ worth of values and then divides them by the number of records found in the range, creating the "Rolling_Average" field.

You need to replace your field names for “DateField" and “ValueField” and add any others you want in the resulting DataSet. Also replace TABLE with the DataSet table you’re using as your input.

It is often beneficial to create a snapshot of values, so that as of any date chosen, you can look at that day’s values compared against other time periods (the day before, week before, month before, etc.). To do this in a DataFlow, the data must be placed side-by-side so the card can read it properly. The following example query does this. Each section should be its own transform. You should also add indexing to the query for MySQL DataFlows.

       rawdata today 
left join
       (select date_add(datevalue, INTERVAL 1 DAY) as date, value from rawdata) yesterday
              on =
left join
       (select date_add(datevalue, INTERVAL 7 DAY) as date, value from rawdata) lastweek
              on =
left join
       (select date_add(datevalue, INTERVAL 1 MONTH) as date, value from rawdata) lastmonth
              on =

The following table shows the logic behind this query:


Some additional work may be needed in order to pull in the correct columns and values (such as using a Group By action).