Difference of values between 2 months
I have a value column named 'Value' and a month column named 'Month'. I want to create a beast mode to calculate the difference between values of the last two months.
So if we have two months in the dataset - Nov 23 and Oct 23. I will want to calculate the difference in value between both months. It is a month-to-month difference. If there is no new month in the dataset like if we are in the Jan 24 month but in the dataset there is no data for Jan 24 then it should print 0.
Best Answers
-
If you have one value per month you can utilize a LAG window function. I've written up about these here:
Alternatively you can restructure your data so that you have a period type (Current, Last Month) and a specific value for your months then utilize some beast modes to calculate the difference. Here's another write up on this method:
A third option is to utilize an ETL and join the data to itself based on a month prior using a formula tile:
`Month` - INTERVAL 1 MONTH
Then join it back onto itself based on the one month prior and the current month and rename your one month prior values as new columns
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Let's assume that the "Month" column has the first date of the month referenced.
The Beast Mode you will want to use is the following:
sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)
if you want to return 0 instead of null for empty values, simply wrap your function in a case expression:
example: case when (function) is null then 0 else (function) end
case when (sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) is null then 0 else (sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) end
If you want to return current month - prior month instead of prior month - current month, just flip the order of the subtraction in the function:
(-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)
Full version using flipped subtraction order:
case when ((-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) is null then 0 else (f(-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) end
I have never tried to use interval 0 month with date_sub, so there's a chance that it might not work. Nothing ventured, nothing gained!
0
Answers
-
If you have one value per month you can utilize a LAG window function. I've written up about these here:
Alternatively you can restructure your data so that you have a period type (Current, Last Month) and a specific value for your months then utilize some beast modes to calculate the difference. Here's another write up on this method:
A third option is to utilize an ETL and join the data to itself based on a month prior using a formula tile:
`Month` - INTERVAL 1 MONTH
Then join it back onto itself based on the one month prior and the current month and rename your one month prior values as new columns
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Let's assume that the "Month" column has the first date of the month referenced.
The Beast Mode you will want to use is the following:
sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)
if you want to return 0 instead of null for empty values, simply wrap your function in a case expression:
example: case when (function) is null then 0 else (function) end
case when (sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) is null then 0 else (sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) end
If you want to return current month - prior month instead of prior month - current month, just flip the order of the subtraction in the function:
(-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)
Full version using flipped subtraction order:
case when ((-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) is null then 0 else (f(-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) end
I have never tried to use interval 0 month with date_sub, so there's a chance that it might not work. Nothing ventured, nothing gained!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive