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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive