Period over Period question on multiple estimates (budget)

HCyamada

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

marcel_luthi

@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

MichelleH

@HCyamada How is your data currently structured?

HCyamada

https://community-forums.domo.com/main/discussion/comment/77667#Comment_77667

Hi Michelle thanks for the response - I added that content above

marcel_luthi

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.

HCyamada

https://community-forums.domo.com/main/discussion/comment/77716#Comment_77716

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?

marcel_luthi

@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

HCyamada

https://community-forums.domo.com/main/discussion/comment/77805#Comment_77805

Marcel - your solution worked perfectly - thanks so much!!!! I resolved the question up here - thanks so much