Dynamic Date Field with Over Function and Balance Forward Question
Please see the attached video to better explain what we are trying to accomplish.
Beast Mode Used:
SUM(SUM((CASE
WHEN `PERIOD` = '2016-01-01' THEN (`Beg Balance`+`AMOUNT`) ELSE `AMOUNT` END)
)
)
OVER(ORDER BY `PERIOD`)
We currently have a balance sheet account where we are trying to calculate the actual balance using the beginning balance and the current month activity. The Beast mode that is currently written using the Over function (see above) works for the entire date range in the data set. However, we are using a static date (2016-01-01) to bring in the beginning balance at the start of the calculation. The issue we are having is building a dynamic date field so that if we select a different year (i.e. 2017 & 2018) it will use the beginning balance from the first January in the date range selected (January 2017) vs. the static date field of 2016-01-01. Attached is an excel file with example data. Any ideas/suggestion on how to solve this issue will be greatly appreciated. Thanks in advance for your help.
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive