Period over Period question on multiple estimates (budget)
Hi community
So I am trying to build a chart where I do have Actual Production by site from 2019-2022 and severall Forecasts for 2023 Full Year
- One Forecast done in 2022year end) - called 2023B
- One Forecast done in 2023Mar - Called 2023MYR
- One Forecast done in 2023Jun - Called 2023LE
I wanted to create a chart that emcompass all 3 2023 forecasts into it and how do they compare vs 2022 - but based on the dataset I cannot "convert" all of the 3 scenarios as 2023 since they will overlap
Simple example as screenshot below - I want to monitor how does 2023 estimates compare vs 2022
Edit:
The data structure is a google sheet for now but it is formatted in that way
Best Answer
-
@HCyamada even if called Tooltip, you can actually use those and display them right on the card but using it as one of the fields in the text options.
As for the it to work, you'll need to tell the FIXED function which filters are allowed, so it will understand you don't want to compare against to global total but totals broken down at different levels, so a combination of the BY and FILER DENY/ALLOW might be needed to make the function responsive, you'll need to play around to find the right combination, but I'd start with a basic BY clause addition:
(SUM(`Volume`)/SUM(SUM(CASE WHEN `Period` = '2022A' THEN `Volume` END) FIXED(BY `Focus Factory`)))-1
0
Answers
-
Hi Michelle thanks for the response - I added that content above
0 -
You could use a beast mode and and a Tooltip to show the data you want (given that you want to show it the same way you currently have it, in a bar chart) but this will compare all of the entries against the one you want.
Your beast mode would look something like:
(SUM(`Volume`)/SUM(SUM(CASE WHEN `Period` = '2022A' THEN `Volume` END) FIXED()))-1
Instead of being a fixed value you could use a variable to input the period you want to compare against to make it more flexible, and then is just to display the result as a tooltip. Let us know if this helps. You might want to consider updating the FIXED function to allow or deny other filters as needed, you can read more about it here.
0 -
Thanks Marcel This is great!!!
The solution by hovering the mouse (tooltip) won't work since this goes to a variety of people and we wanted to have this number explicit on the chart (one Pager)
So in order to work around I tried opening another Card with Pivot Table and looking at the numbers the Bottom line is Correct but the drilldown by Focus Factory is strange
How can we change that code to include that drilldown?
0 -
@HCyamada even if called Tooltip, you can actually use those and display them right on the card but using it as one of the fields in the text options.
As for the it to work, you'll need to tell the FIXED function which filters are allowed, so it will understand you don't want to compare against to global total but totals broken down at different levels, so a combination of the BY and FILER DENY/ALLOW might be needed to make the function responsive, you'll need to play around to find the right combination, but I'd start with a basic BY clause addition:
(SUM(`Volume`)/SUM(SUM(CASE WHEN `Period` = '2022A' THEN `Volume` END) FIXED(BY `Focus Factory`)))-1
0 -
Marcel - your solution worked perfectly - thanks so much!!!! I resolved the question up here - thanks so much
0
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
- 710 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