Organic Growth Calculation
I'm trying to calculate the organic growth. I know in order to do so, I'll need to determine the following:
Yr Ending Assets: CASE WHEN [REPORTING_CAL_DATE]=#2016-12-30#
OR [REPORTING_CAL_DATE]=#2017-12-29#
OR [REPORTING_CAL_DATE]=#2018-12-31#
OR [REPORTING_CAL_DATE]=#2019-12-31#
OR [REPORTING_CAL_DATE]=#2020-12-31#
OR [REPORTING_CAL_DATE]=#2021-12-31#
OR [REPORTING_CAL_DATE]=#2022-3-31#
THEN [Ending Assets] END
Prior-Year Assets: LOOKUP(SUM([Yr Ending Assets]),-1)
Organic Growth: SUM([Flows])/[Prior-Year Assets]
I'd like the end user to be able to see the organic growth for every subcategory. The data also has every month in between.
Please feel free to suggest a different way to calculate this. Thank you in advance.
Answers
-
How is your data formatted? Is it summarized on the reporting date or are each date a snapshot of the values?
I'd recommend using a MagicETL dataflow to calculate the maximum data for each year (calculate the year with a formula tile YEAR(`date`) then pass it into a group by grouping on the year and select the max date value) then filter your original dataset to where those dates are the same. Then inner join back to your original dataset based on the year calculated field in both. This will get you the assest for that year. Then do another join to join your original dataset to the asset year join you just did but base it on the year - 1 (create a new calculated field and subtract 1 from the year and call it last_year) Now your dataset should have the prior year's assets for each month of reporting.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**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
- 60 App Studio
- 41 Variables
- 688 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 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