How to calculate last year's average to use in this year's projections
I'm trying to calculate the average fee per event earned for the previous year and add that as a constant to a dataset to use to calculate projected fees for the upcoming quarter. I can calculate the constant by using Group By to get the average fee by year and then adding a filter formula to isolate the prior year but how do I join that to my original data set as a column? Or perhaps I'm going about this the wrong way and there is a better solution.
Best Answer
-
You'll need to join the single value to every row in the dataset. To do this, create a constant value, you could call it 'Join column 1' and it could be the number 1 or any other value, and include it in your group by. Then split out a new flow from WC WCB Activities and create a constant value column called something like 'Join column 2' that matches the value you created in 'Join column 1' and then join on Join Column 1 = Join column 2. Now you'll have that average value on every row of your data to compare with. If you need different values on different rows by year or month or whatever, then you would split out the dataflow as mentioned and join on year = year or year + month = year + month, etc…
If I solved your problem, please select "yes" above
1
Answers
-
You'll need to join the single value to every row in the dataset. To do this, create a constant value, you could call it 'Join column 1' and it could be the number 1 or any other value, and include it in your group by. Then split out a new flow from WC WCB Activities and create a constant value column called something like 'Join column 2' that matches the value you created in 'Join column 1' and then join on Join Column 1 = Join column 2. Now you'll have that average value on every row of your data to compare with. If you need different values on different rows by year or month or whatever, then you would split out the dataflow as mentioned and join on year = year or year + month = year + month, etc…
If I solved your problem, please select "yes" above
1
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