Next Up Summary #
I want to write a summary number that notes the "Next Up" City in my data.
X axis are city names (chronological sorted on Closing Date), Y Axis are values. Some of those cities are Closed, some are Ongoing - I can't use the series to break that status out visually by color, because I'm using a dual Y axis to show two sets of metrics values.
Can someone help me write a summary number that prints the name of the City who has the next upcoming "Closing Date"?
If I have the cities below in my data:
Denver January 20
Atlanta February 5
Boston February 10
Cincinnati May 1
I want the summary number to say: "Next Market: Atlanta"
The logic is the City with the minimum Closing date in the future.
Thanks!
Best Answers
-
I'm fairly certain you'll need to calculate the Next City ahead of time.
You could accomplish this by doing the following:
Transform 1:
SELECT MIN(CASE WHEN `ClosingDate` > CURDATE() THEN `ClosingDate` END) AS 'MinDate'
FROM Dataset
Transform 2:
SELECT CONCAT('Next City: ', a.City) AS 'NextUp'
FROM Dataset AS a INNER JOIN transform1 as b ON a.ClosingDate = b.MinDate
Final Transform:
SELECT *, (SELECT NextUp FROM transform2) AS 'SummaryNumber'
FROM DatasetThen at your card level, create a beast mode that says:
MAX(`SummaryNumber`)
Hopefully that gets you what you're looking for.
Let me know if you have any issues,
Valiant
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.3 -
You can try putting the whole thing in a beast mode. You'll notice the seemingly pointless concatenation, but that is the trick to making the beast mode available as a summary number.
CONCAT(MIN((CASE WHEN `Date`>CURRENT_DATE() THEN `City` END)),'')
If you name the beast mode 'Next Up', it will show the city name and say 'Next Up' after it!
I need one more solution to get a badge, so I hope this does it! ?
@TacoShelly - SO glad we explored beast mode summary numbers this week!
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Answers
-
I'm fairly certain you'll need to calculate the Next City ahead of time.
You could accomplish this by doing the following:
Transform 1:
SELECT MIN(CASE WHEN `ClosingDate` > CURDATE() THEN `ClosingDate` END) AS 'MinDate'
FROM Dataset
Transform 2:
SELECT CONCAT('Next City: ', a.City) AS 'NextUp'
FROM Dataset AS a INNER JOIN transform1 as b ON a.ClosingDate = b.MinDate
Final Transform:
SELECT *, (SELECT NextUp FROM transform2) AS 'SummaryNumber'
FROM DatasetThen at your card level, create a beast mode that says:
MAX(`SummaryNumber`)
Hopefully that gets you what you're looking for.
Let me know if you have any issues,
Valiant
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.3 -
You can try putting the whole thing in a beast mode. You'll notice the seemingly pointless concatenation, but that is the trick to making the beast mode available as a summary number.
CONCAT(MIN((CASE WHEN `Date`>CURRENT_DATE() THEN `City` END)),'')
If you name the beast mode 'Next Up', it will show the city name and say 'Next Up' after it!
I need one more solution to get a badge, so I hope this does it! ?
@TacoShelly - SO glad we explored beast mode summary numbers this week!
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
Awesome!! Thank you @DataMaven!!! This Beast Mode was exactly what I needed. I didn't realize I could put the Min in that position, that's where I was getting stuck.
And thanks as well to @Valiant, much appreciated.
Broadway + Data1 -
@DaniBoy Is it possible to label @Valiant's answer as a solution, too, or can there be only one? He was the one who came up with using the MIN function. I wouldn't have been able to solve it if he hadn't already gotten it started!
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Actually... it's grabbing the incorrect city, I think it's picking the City name Alpha (min A-Z) rather than by the minimum date in the future:
It should be showing Milwaukee as the Next Market based on the dates:
CONCAT('Next Market: ',MIN((CASE WHEN `Closing Date`>CURRENT_DATE() THEN concat(`City`,' ',`Closing Date`) END)),'')
Broadway + Data0 -
Absolutely! All done.
Dani
1 -
YAY!!! Teamwork!
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
EEK - yes it is - duh! It does need to be date where I put City. I meant to test that, but then my brain said the city was the right answer because it was for that sub-set. Working on better anwer!
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
@RobynLinden @Valiant - This seems like it should almost do it, but I think Valiant could be right. Beast Modes are too adaptive, so I think it's not summarizing. That would mean that it needs to be done in the ETL. His is showing in SQL, but it would be easy in MagicETL, too. Let me know if you are going that way, and need any assistance.
Meanwhile - @Valiant - Am I missing something in this beast mode that could help?
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction 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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive