More Efficient YoY Actual to Budget View

Data_Devon · May 23

Hi Domo Sapiens,

Currently, we have a year over year graph that has current year budget also in the series:

The bar graphs are each year's actual values, and the line is the current year's budget value.

I hard-coded each series with a beastmode, as such:

CASE WHEN YEAR(Date) = '2025' AND Actual or Budget = 'Actual' THEN Charge Quantity

ELSE 0

END

CASE WHEN YEAR(Date) = YEAR(CURDATE()) AND Actual or Budget = 'Budget' THEN Charge Quantity

ELSE 0

END

Unfortunately, this renders the date filtering useless since each year is hardcoded in the above beastmode.

Best case scenario: I can use the Period over Period chart so that the Date can be filtered by the native "Choose Date" filter box. However, I can't figure out how to include the budget series when using a PoP chart, because it lumps it in with the year.

Any ideas?

GrantSmith · May 23

Hi @Data_Devon - I'd recommend restructuring your data to allow more flexible period over period analysis. I've done a write up here with it: https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

Data_Devon · May 23

Thank you @GrantSmith !

I may have misunderstood your write up, but all the solutions you shared are for when you have one value but multiple periods assigned to the value.

I have the opposite problem - 1 date with multiple values assigned (budget and actual)

Did I misunderstand your write up?

GrantSmith · May 23

You'd have two separate beast modes for each different metric and the offset type

For example Current Year's Budget:

CASE WHEN `Period Type` = 'Current' AND `Actual or Budget` = 'Budget' THEN `Charge Quantity` END

Last Year's Budget

CASE WHEN `Period Type` = 'Last Year' AND `Actual or Budget` = 'Budget' THEN `Charge Quantity` END

You can then use these beast modes as Y-Axis values to display them on your chart. You'd likely need to define your own 2 year ago offset. This will then give you a single Date field you can use to leverage the date filtering and still display data relative to the filtered date.

Data_Devon · May 23

Awesome, thank you @GrantSmith . I think we are getting close. The flexibility of using the dynamic "CURDATE()" instead of hardcoding a date value is awesome.

In order to use the native "Choose Date" & "Graph By" filter that is native to each dashboard, I need to include the Date as the X Axis:

I think we're getting closer, but now I can't see the "year over year" view that I was originally hoping for - with each month next to the same month of the prior year.

I hope this makes sense. Thanks for your continued support.

DavidChurchman · May 23

I think I'm going to suggest something similar (identical?) to GrantSmith, but maybe easier because instead of flexible periods, it's just YoY.

Instead of a long dataset feeding your card that has all of your dates on top of each other, you can restructure your data to use just this year's data, with additional columns for past years. Then you can change the granularity and use real dates in your filter (this year's dates):

Here it is at a different granularity so you can see the dates are working:

I'm sure there are smarter ways to do this, but one approach is using MagicETL:

Use Domo's calendar dataset to get a list of every day in the year/years of interest. You can use a filter formula like year(dt) = year(currdate())
If not already aggregated by date, aggregate your current data by date, or if you don't want to aggregate it, create a column that is formatted as a date
Left join your data to the calendar data to get data for every day so far this year (and nulls for the other dates)
Add a year to the date in your data, rename columns, and left-join it again to get data for the prior year for all matching days this year. Now you should have something like: Date | This Year's Profit | Last Year's Profit
Repeat as needed for another year or years

More Efficient YoY Actual to Budget View

Answers

Categories