Max Rank by Month and Year for Volume Metric
Hi,
I was hoping to write a case statement that would allow me to highlight the month with the highest Volume. Additionally, I want to take data from that particular month and I want to graph it by Day.
On the same graph I want to highlight the Volume for the current month as well.
Ideally I would do this in a line chart.
I was hoping someone could help me write this beast mode or guide me in the correct decision.
Best Answer
-
First, what does the raw data looks like? and then if you can do a doodle or something of what your desired visual would be? The reasons I'm asking this is because it seems to me you're talking about 3 different cards which you can put together in a dashboard to show all these entries.
There are also some key things you might want to consider, like the top month overall might be say Feb2023, but if you apply a filter to only a subset of the data this might no longer be true, and March2023 might have a higher volume after the filter is applied, so when you say Month with the Highest Volume do you mean overall or for the specific data you're looking into? This changes whether this needs to be done via ETL or a Beast Mode. Also when you're talking about Highest, is this highest all time or highest on a given window, and if the latter, is that window fixed like Last 6 Months or 1 Year, or is this something you expect your users to be able to adjust via a variable?
Taking a shoot in the dark, you'll need at least 2 Beast Modes for this:
Beast Mode to classify all entries as either "Current Month", "Highest Month" or "Other", this will be used to filter so that you only show entries for the first 2 options.
CASE
WHEN DATE_FORMAT(`dateColumn`,'%y%m')=DATE_FORMAT(CURDATE(),'%y%m') THEN 'Current'
WHEN <LOGIC TO IDENTIFY HIGHEST MONTH> ELSE 'Other'
ENDOne that will tell you the day of the month each date falls into
DAY(`dateColumn`)
With this you'd build your card using the 1st BeastMode as your
Series
and the second as yourX axis
withValues
being whatever you're measuring (in this case volume).The only downside with this approach is that if the Current Month also happens to be the Highest, only a single bar/line will be presented.
I'd strongly suggest the flag for highest month to be handled via ETL as there you can use the
Rank and Window
tile for this purpose and your Beast Mode logic will be kept fairly simple, of course this only works if the Highest should be the global one and not one that is responsive to filters.1
Answers
-
First, what does the raw data looks like? and then if you can do a doodle or something of what your desired visual would be? The reasons I'm asking this is because it seems to me you're talking about 3 different cards which you can put together in a dashboard to show all these entries.
There are also some key things you might want to consider, like the top month overall might be say Feb2023, but if you apply a filter to only a subset of the data this might no longer be true, and March2023 might have a higher volume after the filter is applied, so when you say Month with the Highest Volume do you mean overall or for the specific data you're looking into? This changes whether this needs to be done via ETL or a Beast Mode. Also when you're talking about Highest, is this highest all time or highest on a given window, and if the latter, is that window fixed like Last 6 Months or 1 Year, or is this something you expect your users to be able to adjust via a variable?
Taking a shoot in the dark, you'll need at least 2 Beast Modes for this:
Beast Mode to classify all entries as either "Current Month", "Highest Month" or "Other", this will be used to filter so that you only show entries for the first 2 options.
CASE
WHEN DATE_FORMAT(`dateColumn`,'%y%m')=DATE_FORMAT(CURDATE(),'%y%m') THEN 'Current'
WHEN <LOGIC TO IDENTIFY HIGHEST MONTH> ELSE 'Other'
ENDOne that will tell you the day of the month each date falls into
DAY(`dateColumn`)
With this you'd build your card using the 1st BeastMode as your
Series
and the second as yourX axis
withValues
being whatever you're measuring (in this case volume).The only downside with this approach is that if the Current Month also happens to be the Highest, only a single bar/line will be presented.
I'd strongly suggest the flag for highest month to be handled via ETL as there you can use the
Rank and Window
tile for this purpose and your Beast Mode logic will be kept fairly simple, of course this only works if the Highest should be the global one and not one that is responsive to filters.1 -
hello,
I ended up using an ETL to complete the task. Thank you!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive