How to Create WTD, MTD, QTD, YTD on one Chart
I received a request to create a chart to show KPI with WTD … YTD on a single chart.
Image below is the example:
The Closest I've gotten is;
I created a dimeson that flags the 'WTD' dates as a Y/N, then created a beast mode doing a case statement for when the Date flags are 'Y.' I've done the same for the other KPI, but it will just make the table wide instead of being able to place them on separate rows
I don't know how to add the separate KPI on different rows.
Best Answer
-
You can accomplish this at the ETL level. You'll need transforms for the WTD, MTD, QTD, YTD ranges along with calculations in each of those transforms aggregating your measurable fields.
1
Answers
-
Last Week:
CASE WHEN WEEK(`DateField`) = WEEK(CURRENT_DATE())-1 THEN 'In' ELSE 'Out' END
MTD:
CASE WHEN `DateField` > LAST_DAY(CURRENT_DATE()) - INTERVAL 1 MONTH AND `DateField` <= CURRENT_DATE() THEN 'In' ELSE 'Out' END
QTD:
CASE WHEN QUARTER(`DateField`) = QUARTER(CURRENT_DATE()) AND YEAR(`DateField`) = YEAR(CURRENT_DATE()) AND `DateField` <= CURRENT_DATE() THEN 'In' ELSE 'Out' END
YTD:
CASE WHEN YEAR(`DateField`) = YEAR(CURRENT_DATE()) AND `DateField` <= CURRENT_DATE() THEN 'In' ELSE 'Out' ENDEtc…
If I solved your problem, please select "yes" above
0 -
Hey @ColemenWilson,
Thanks for the reply, but I think the Beast mode makes sense, but I cant position the different KPI on Separate rows. The request is to get WTD sales in Column 1, and Sales on Row 1, and Plan on Row 2
I'm currently using the mega table visual, and can't figure out how to break out the KPI by Time slice
0 -
You can accomplish this at the ETL level. You'll need transforms for the WTD, MTD, QTD, YTD ranges along with calculations in each of those transforms aggregating your measurable fields.
1 -
Re-read your initial question - you would need to do some work in ETL as @jessdoe pointed out. You can re-purpose your beastmodes to save time. Just use the formula tile in magic.
If I solved your problem, please select "yes" above
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
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 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