Current Year, Prior Year, and Budget on Same Card
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
Comments
-
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
If possible, could you share an example of a card that used this solution?
Thank you!
0 -
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.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 302 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 633 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 760 Beast Mode
- 62 App Studio
- 42 Variables
- 699 Automate
- 181 Apps
- 457 APIs & Domo Developer
- 51 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive