Rolling Average Calculation

Options
TimManders
TimManders Member
in Magic ETL

I have a set of monthly data and would like to calculate a 3-month rolling average. For example:

Jan = 30

Feb = 32

Mar = 34

April = 36

 

Rolling average for March would be 32 ((30+32+34)/3), for April would be 34 ((32+34+36)/3).  It is probably something fairly simple, but I'd appreciate your help.

 

Thank you.

Best Answer

  • zcameron
    zcameron Contributor
    Answer ✓

    A Beast Mode is calculated on either a single record or group of records, depending on how the data on the card is being grouped. Because of that, the calculation doesn't have a way of reference other records. For example, I can't say, "I'm looking for the April average. Look back at other records that come before April and sum them up for me." You could create a card that does a quarterly average, though. To do that, set your date grain to "by Quarter" at the top of the card, then add your date and value columns to the X and Y axis, respectively. Then set your aggregation option for your value column to AVG.

     

    Alternatively, you'd need to do the calcuation you're looking for in a Dataflow.  The transform would look something like this:

     

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

    FROM
      TABLE t1

     

     

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

    We're using a subquery to go grab the sum of the last 3 months worth of values and then dividing them by the number of records it found in that range and calling that the Rolling_Average field. You'd need to replace your field names for `DateField` and `ValueField` (and add any others you want in the resulting dataset) and replace TABLE with the dataset table you're using as your input.

     

    I hope that helps!

Answers

  • zcameron
    zcameron Contributor
    Answer ✓

    A Beast Mode is calculated on either a single record or group of records, depending on how the data on the card is being grouped. Because of that, the calculation doesn't have a way of reference other records. For example, I can't say, "I'm looking for the April average. Look back at other records that come before April and sum them up for me." You could create a card that does a quarterly average, though. To do that, set your date grain to "by Quarter" at the top of the card, then add your date and value columns to the X and Y axis, respectively. Then set your aggregation option for your value column to AVG.

     

    Alternatively, you'd need to do the calcuation you're looking for in a Dataflow.  The transform would look something like this:

     

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

    FROM
      TABLE t1

     

     

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

    We're using a subquery to go grab the sum of the last 3 months worth of values and then dividing them by the number of records it found in that range and calling that the Rolling_Average field. You'd need to replace your field names for `DateField` and `ValueField` (and add any others you want in the resulting dataset) and replace TABLE with the dataset table you're using as your input.

     

    I hope that helps!

  • zcameron
    zcameron Contributor

    Tim, were you able to try out the solution I proposed? Did it work for you?

  • TimManders
    TimManders Member

    Thank you. I have not been able to test this yet, but the logic makes perfect sense, both about how to build cards with appropriate date grains, and that DataFlow is probably the best option for flexibility.

  • Unknown
    Unknown

    Hi zcameron, 

    I'm trying to accomplish the same rolling total but not for a 3-month interval. Instead, I need to sum the last 13 periods (each year is divided into 13 fiscal periods) for each period on the chart. So for example, when the x axis is showing Period 13 in 2016 then the y axis value should be the total value of Period 1 through Period 13 in 2016. Any help is appreciated! Thanks!

  • user01384
    user01384 Member

    Welp, it's 3 years later and I'm having a similar issue.  

    I'd like to perform a weighted rolling average (say ROI), based on an underlying calculation.  So, for example, 

     

    MONCostSalesROI
    Jan1002002
    Feb1002502.5
    Mar1502501.666667
    Apr2502751.1
    May1001501.5
    Jun100900.9

     

    window: 0, -3

    MonRolling ROI
    Jan2
    Feb2.25
    Mar2
    Apr1.55
    May1.35
    Jun1.144444

     

    Am I right that the only solution available is through a custom query to the db?  Does Domo not have a built in method for this type of thing?  I'm not sure I'd consider a rolling average 'advanced analytics', so just seeing whether Domo has an option here other than going back to the database (rather than using a domo supported feature)

     

    My big concern is that this isn't necessarily very scalability using a custom query for each and every level of detail in a dashboard.  Quarterly window?  that's another query.  Yearly window?  That's another query.  Daily window? that's another query.  Am I missing something here?  Maybe this is on the roadmap or a feature request somewhere?

  • zcameron
    zcameron Contributor

    The supported methods of accomplishing a rolling average are handled in the ETL layer of Domo. Here's a link to the Help Center Article that describes how to accomplish it in MySQL, Redshift, and Magic ETL.

    http://knowledge.domo.com/?cid=createrollingaverage

     

    I hope that helps!

  • JR_S1L
    JR_S1L Member

    I am attempting finding the rolling average of a dataset containing units, date, and name. There are multiple units per name per date.

    How do I calculate the rolling average for each name?

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    https://dojo.domo.com/discussion/54637/rolling-average-for-each-category


    https://www.youtube.com/watch?v=cnc6gMKZ9R8

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories