Is there a formula that I can use for the month to month change?

I have the below table, and I would like to add a column for the month-to-month change by amount and %. Is this possible?
Answers
-
You can use a lag function.
Final result:Step 1:
Your data must have some field that orders the data. Month alone won't work, you have to have a numeric or date field. I've added dates to your sample data.Step 2:
Create a beastmode that is the Previous Value: LAG(Total
) OVER (ORDER BYDate
)Step 3:
Create a beastmode to calculate the variance amount:Total
-Previous value
Step 4:
Create a beastmode to calculate the variance percentage: (Total
-Previous value
)/Previous value
You don't need to break these steps out, and can instead have a single beastmode if you prefer. I just wanted to show you step by step.
If I solved your problem, please select "yes" above
0 -
Thank you! Is the previous month a column I need to add? Or is there a formula etc, that I can use?
0 -
No you just need the current month. The previous month is derived in the lag function.
However, `Previous Value` is what I named the beastmode in step 2. In steps 3 and 4 I use a nested beastmode that references the beastmode called `Previous Value` created in step 2.
If I solved your problem, please select "yes" above
0 -
I am getting the below error.
0 -
That is not an error, only a warning. However, I do see that you need to update the beastmode to use your Total field and your Date field.
Is your data aggregated at the level you first shared? If not, you will need to first aggregate or use a group by.
If I solved your problem, please select "yes" above
0 -
I think that depends upon whether you use aggregates in your columns.
Month,Dept 1,Dept 2,Dept 3,Dept 4,Dept 5,Total
November,132.0,251.0,59.0,477.0,70.0,989.0
December,127.0,250.0,57.0,366.0,68.0,868.0
January,123.0,235.0,53.0,391.0,68.0,870.0
February,128.0,233.0,58.0,311.0,69.0,799.0
March,124.0,224.0,59.0,401.0,67.0,875.0
April,128.0,222.0,60.0,380.0,64.0,854.0
May,109.0,211.0,49.0,355.0,68.0,792.0
Avg Headcount,124.4,232.3,56.4,383.0,67.7,863.9** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
lol…sorry about mixing the avg headcount in the months. Obviously, I was straight typing off sample data.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 667 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 873 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive