YOY in table card beast mode
Comments
-
Your Beastmode looks good, but you can't use the card's date selection because it is year specific. In essence when you choose 'This Month' then you filter out all data from last year.
Consider these beastmodes with no date filter:
`This Year`
CASE
WHEN YEAR(CURRENT_DATE()) = YEAR(`Date`)
THEN `Net Sales`
END
`Last Year`
CASE
WHEN YEAR(CURRENT_DATE()) - 1 = YEAR(`Date`)
THEN `Net Sales`
END
`Month` (for filtering)
CASE
WHEN MONTH(CURRENT_DATE()) = MONTH(`Date`)
THEN 'This Month'
ELSE 'Other'
ENDThen add a FILTER using the new beastmode `Month` and filter to 'This Month' which is not year specific.
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
@pauljames there are a lot of simple reasons that get in the way. Without more detail it is hard to say why that might be in your case.
First, the above formula lets you see them side by side, but to compare them it would have to be aggregated. The same record cannot be both this year and last year, but if you use a SUM to aggregate the rows it could compare the two.
-- `This Year`
SUM(
CASE
WHEN YEAR(CURRENT_DATE()) = YEAR(`Date`)
THEN `Net Sales`
END) -
`Last Year`
SUM(
CASE
WHEN YEAR(CURRENT_DATE()) - 1 = YEAR(`Date`)
THEN `Net Sales`
END)
Even that leaves lots of 'gotcha' concerns. The biggest one is grouping. Aggregate grouping is automatically affected by the card settings. In a table view all "non-aggregated" columns will form the group basis. So if you show
Year(`Date`)
as a column then each row can only use data from a single year. Nullifying the effect of the logic shown above.This is hidden further in any other chart type. If you have a date grain on a bar chart for example, this will never work because a record from last year and a record from this year won't exist in the same date grain. Thus prohibiting the beast mode (which gets the data last after filters and grouping) from ever seeing records from both years to do math with them.
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 688 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive