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
-
In that case, you could modify the second transform to look like this:
SELECT
`MONTH`
, (SELECTSUM(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 mThis will include the records that are within 6 months of the date in question.
I hope that helps!
1
Answers
-
We are looking into this and will get back to you shortly.
Regards,
0 -
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`
, (SELECTSUM(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 mYou'll need to swap out your own column and table names, of course, but hopefully this helps you get started.
0 -
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?
0 -
Last 6 months would be great.
0 -
In that case, you could modify the second transform to look like this:
SELECT
`MONTH`
, (SELECTSUM(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 mThis will include the records that are within 6 months of the date in question.
I hope that helps!
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive