Calculating period over period change in Domo
I've been trying to find a solution for easily calculating percent change of metrics between two periods and put them in a table. Like "What is the percent change for "costs" between this month, and last month?"
Normally this is easy to calculate outside of Domo using the standard formula:
(V2 - V1) / V1) *100
However, translating this into a beast mode for Domo doesn't seem possible. There are some cards that do show percent change, but they are very rigid, and limited to one metric as a scorecard or byline on a line graph.
Understanding how all your key metrics have changed since last month in a single table feels like a basic request. Is it really not possible create this fundamental type of reporting in Domo's visualizer?
Best Answer
-
If you have more than one year of data you'll need to account for the year, and not only the month. You can use the LAST_DAY() function to normalize your dates:
— % Change Costs
(
— current month costs
SUM(CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(CURRENT_DATE()) THEN `Costs` END)
-
— prior month costs
SUM( CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(DATE_SUB(CURRENT_DATE() INTERVAL 1 Month)) THEN `Costs` END)
)
/
— prior month costs
SUM( CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(DATE_SUB(CURRENT_DATE() INTERVAL 1 Month)) THEN `Costs` END) * 100
1
Answers
-
hey strostle,
POP is very simple in domo using the case statement, date sub, and current_date() functions:
sum(case when month(date) = month(date_sub(current_date(), interval 1 month)) then 'revenue' end) / sum(case when month(date) = month(current_date()) then 'revenue' end)
0 -
If you have more than one year of data you'll need to account for the year, and not only the month. You can use the LAST_DAY() function to normalize your dates:
— % Change Costs
(
— current month costs
SUM(CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(CURRENT_DATE()) THEN `Costs` END)
-
— prior month costs
SUM( CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(DATE_SUB(CURRENT_DATE() INTERVAL 1 Month)) THEN `Costs` END)
)
/
— prior month costs
SUM( CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(DATE_SUB(CURRENT_DATE() INTERVAL 1 Month)) THEN `Costs` END) * 100
1 -
Thanks ggenovese, your solution worked and I appreciate the added detail. I wish Domo had a more natural way to produce these results when multiple metrics are on a table, but I think setting up some variables and/or additional beast modes will make that possible.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K 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
- 745 Beast Mode
- 58 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
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 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
- 108 Community Announcements
- 4.8K Archive