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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive