Previous Week Amounts
Hello, the ultimate goal of my beastmode is to write a beastmode that will provide the variance week over week for total Net Cost. If you look at the attached picture, all I need is a beastmode that would produce the yellow column — the total Net Cost for the previous week. From there it'll be easy to then make a beastmode for the variance between the first two columns.
The dataset I'm working with has multiple rows for each Week Start Date. So to get Net Cost for a given week, you have to use a CASE WHEN to isolate that Week Start Date, then SUM the Net Cost.
We also have a useful column in the dataset called Cumulative Week #. It's just a constant for each different Week Start Date that counts up by 1 each week.
Appreciate any ideas for the most efficient way to do this. Thank you!
Answers
-
The issue is that you have multiple values for the same week so using a LAG window function won't work in your case. When I'm dealing with period over period analysis I'll utilize my own date dimension table and utilize beast modes to calculate the period over period (in your case week over week). You can read more on this here:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith definitely thought about ETL work but the dataset is the company's biggest and I don't anticipate being able to make any additions there column-wise. So is there no way to do this with a Beastmode, without amending the dataset?
0 -
@GrantSmith is there any way to do this with a beastmode given the dataset structure laid out? Thank you for your help
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive