Rank and Window? Lag?
So I have a dataset that captures quarterly expense data.
I have been tasked with adding an 'expense ratio change column'
Basically, I need to know the % change of expenses changed from quarter to quarter by project
I know the math on this ((p2 - p1) / (p1)) but am not sure how to tile it in domo to make sure it is taking into account the 'project_number' (not shown)
Any suggestions? I need to create a column in the dataset btw, this is important
If this helps, feel free to agree, accept or awesome it!
Best Answer
-
The Lag function should be used to add a column to hold the prior quarter expenses. In your first screenshot, name your new column "Previous Quarter Expenses" (or something similar), change the function drop-down to "Lag", run the function on your current quarter expense column, with an offset of 1.
Once you've created this column, then I would add your variance calculation as a beast mode in your card like this:
(sum(`Current Quarter Expenses`) - sum(`Previous Quarter Expenses`))/sum(`Previous Quarter Expenses`)
2
Answers
-
@MichelleH So here is my first attempt at creating a sequential column - this gave me that 'Latest Date' column above. I'm not sure where to go from here so that I can take expenses from ( (latest date 1) - (latest date 2) ) / latest date 2 and so on down the line (e.g. (p2-p3)/p3, (p3-p4/p4), etc
If this helps, feel free to agree, accept or awesome it!
0 -
The Lag function should be used to add a column to hold the prior quarter expenses. In your first screenshot, name your new column "Previous Quarter Expenses" (or something similar), change the function drop-down to "Lag", run the function on your current quarter expense column, with an offset of 1.
Once you've created this column, then I would add your variance calculation as a beast mode in your card like this:
(sum(`Current Quarter Expenses`) - sum(`Previous Quarter Expenses`))/sum(`Previous Quarter Expenses`)
2
Categories
- All Categories
- Product Ideas
- 2K Ideas Exchange
- Connect
- 1.3K Connectors
- 308 Workbench
- 7 Cloud Amplifier
- 10 Federated
- Transform
- 661 Datasets
- 117 SQL DataFlows
- 2.2K Magic ETL
- 820 Beast Mode
- Visualize
- 2.5K Charting
- 84 App Studio
- 46 Variables
- Automate
- 193 Apps
- 483 APIs & Domo Developer
- 85 Workflows
- 23 Code Engine
- AI and Machine Learning
- 22 AI Chat
- 3 AI Projects and Models
- 18 Jupyter Workspaces
- Distribute
- 116 Domo Everywhere
- 282 Scheduled Reports
- 11 Software Integrations
- Manage
- 142 Governance & Security
- 9 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 115 Community Announcements
- 5K Archive