Current Year, Prior Year, and Budget on Same Card

user054650 · June 2020

Hello,

I'm trying to build a car with Budget, Current Year Actuals, and Prior Year Actuals. I created the view I wanted (see attachment), however, I had to use BeastMode. I can't filter easily with the way I had to write the BeastMode.

PROBLEM: Create a card with CY Actuals, PY Actuals, and Budget on one card with the ability to adjust Page Filters and have the card update.

Note: I've tried the Period over Period chart type without success. I have Budget as part of the underlying data, but can't get it to show up. There is not an option for "Series" in the Period over Period chart type.

BEASTMODE:

CASE
when `Order Amount (Global)` and YEAR(`Date`) = YEAR(CURRENT_DATE()) then 'CY Actuals'
when `Order Amount (Global)` and Year(`Date`) = YEAR(CURRENT_DATE())-1 then 'PY Actuals'
when `Orders Budget (Global)` and YEAR(`Date`) = YEAR(CURRENT_DATE()) then 'Budget'
Else '2+ Years'
end

YoY and Budget.PNG

GrantSmith · June 2020

Hi @user054650

@jaeW_at_Onyx has had several writeups about this specific problem but it boils down to you want a new table which has an actual date, a report date and an offset/period type. (Read more about it here: https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Calculating-index-vs-Year-Ago/m-p/48952)

So you'd have

| Actual | Report | Offset |

| 1/1/2020 | 1/1/2020 | Current Year |

| 1/2/2020 | 1/2/2020 | Current Year |

| 1/3/2020 | 1/3/2020 | Current Year |

...

| 1/1/2020 | 1/1/2019 | Prior Year |

| 1/2/2020 | 1/2/2019 | Prior Year |

| 1/3/2020 | 1/3/2019 | Prior Year |

In a separate table and join to this table so you can filter / beast mode based on the offset type. You'd want to utilize three separate beast modes as well instead of a single beast mode.

As for your card you can utilize a Line Bar chart to get what you're looking for.

user054650 · June 2020

If possible, could you share an example of a card that used this solution?

Thank you!

jaeW_at_Onyx · June 2020

https://www.youtube.com/watch?v=CDKNOmKClms

Here you go @user054650 . If you have further questions, check out some of the videos on my channel!

If you're looking for one on one support, I am available at a reasonable rate to provide consulting services, reach out to me at jae@onyxreporting.com.

BTW @GrantSmith , in your solution you put the offset in the Report Date column. The offset should go into the Activity Date.

Charting

Charting

Current Year, Prior Year, and Budget on Same Card

Welcome!

Comments

Welcome!

Quick Links

Categories