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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 600 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 685 Beast Mode
- 43 App Studio
- 38 Variables
- 654 Automate
- 169 Apps
- 438 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 383 Distribute
- 110 Domo Everywhere
- 267 Scheduled Reports
- 6 Software Integrations
- 111 Manage
- 108 Governance & Security
- 8 Domo University
- 25 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive