# Monthly Total Running Average Rates

Hi,

I have a sum function within a beast mode, that calculates a simple rate and I am trying to create a total running average rate, but up until now, I am only succeeding in getting the right values for each month. When I use the running total line chart, the percentage gets bigger than 100 %.

However, I want to sum up the values from each previous month and divide them accordingly, so that I can get the average rate from the beginning of the particular activity up until now. Example (assuming equal numbers in January and February):

Data now:
January: 100% conversion
February: 50% conversion

Desirable result: (Monthly Total Running Average):
January: 100% conversion
February: 75%

Is this possible within the Beast mode or could you provide us with an example of a SQL code, that could bring us the desirable results?

## Best Answer

• admin
Answer ✓

In that case, you could modify the second transform to look like this:

SELECT
`MONTH`
, (SELECT

SUM(yt.`conversions`) / SUM(yt.`opportunities`)

FROM

your_table yt

WHERE

LAST_DAY(yt.`start`) <= m.`Month`

AND LAST_DAY(yt.`start`) >= DATE_SUB(m.`Month`, INTERVAL 6 MONTH)

) AS `Running Average

FROM
months m

This will include the records that are within 6 months of the date in question.

I hope that helps!

## Answers

• domo

💎

We are looking into this and will get back to you shortly.

Regards,

Dani aka "Mr.Dojo"

Dojo Admin
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
**You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.
• admin

Unfortunately, the desired result isn't possible in beast mode because each record will fall into exactly one category, which in this case appears to be the month. If a record is used in the January line point, it isn't also part of the group of records used to calculate the February line point.

You could create a dataset that behaves how you'd like using a dataflow, however. You might try something like this:

Transform 1: months

/* Selects distinct month values from the data for use in the next transform */

SELECT

DISTINCT LAST_DAY(`date`) AS `Month`

FROM

your_table

ORDER BY

`Month`

Transform 2: running_averages

/* Uses a sub query to calculate averages from the beginning of the year through the month of each record in the months table */

SELECT
`MONTH`
, (SELECT

SUM(yt.`conversions`) / SUM(yt.`opportunities`)

FROM

your_table yt

WHERE

LAST_DAY(yt.`start`) <= m.`Month`

AND LAST_DAY(yt.`start`) >= STR_TO_DATE(CONCAT(YEAR(m.`Month`),'-01-01'),'%Y-%m-%d')) AS `Running Average

FROM
months m

You'll need to swap out your own column and table names, of course, but hopefully this helps you get started.

• @zcameron, thank you for the solution.

I implemented it and it works fine, but when a new year begins, the rolling average starts from January and is not cumulated with December from the last year.

What could be the issue?

• admin

This code is set up to give you Jan through Dec metrics. Can you clarify what your criteria are for what period should be included in the rolling average, e.g. instead of for the year, do you want to see the last 6 months?

• Last 6 months would be great.

• @Simon, tagging you to check out zcameron's latest reply.

This discussion has been closed.