Adding a New Column to Calculate Difference From Week to Week
I am trying to add an extra column that will calculate the difference in order volumes from week to week. See attached screenshot for details:
Week 1 -> Week 2 difference is 7394
Week 2 -> Week 3 difference is 53
Week 3 -> Week 4 difference is 452
Basically using the current week's number and calculating the difference between the # of orders and the prior week. I am not seeing a difference function available in Domo.
Is there a Domo tool that will do this, or will I need a beast mode?
Any help is greatly appreciated!
Domo supports window functions (you need to have the feature turned on in your instance. Talk with your CSM if you don't have it enabled) which can get you the prior week's value using the LAG window function. The caveat to this is that it assumes there are no missing week in your dataset.
LAG(SUM(`Orders`)) OVER (ORDER BY YEAR(`OpenedDate`) * 100 + WEEKOFYEAR(`OpenedDate`))
Simply Put, order my rows by the year and date (some simple math so that it ends up being sorted in the correct way YYYYWW) and get the total from the prior week.
To then get the difference you can then take the sum for the current week and subtract the value from the prior week.
SUM(`Orders`) - LAG(SUM(`Orders`)) OVER (ORDER BY YEAR(`OpenedDate`) * 100 + WEEKOFYEAR(`OpenedDate`))**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
while lag() is a brilliant solution for getting this done, keep in mind that lag will get the previous row. IF THERE IS A GAP IN WEEKS you'll end up comparing week 7 with week 5. which ... depending on the business question may or may not be correct.
If you want to calculate current week versus previous week, build an offset date calendar like in the video below, it'll consistently allow you compare current week to previous week.Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 473 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 209 Visualize
- 257 Beast Mode
- 2.1K Charting
- 12 Variables
- 85 Cards, Dashboards, Stories
- 18 Automate
- 355 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 183 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive