Next Up Summary #

Reply
Highlighted
Blue Belt

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!

Broadway + Data

Accepted Solutions
Black Belt

Re: Next Up Summary #

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 Dataset

Then 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.

Major Brown Belt

Re: Next Up Summary #

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!  :->  

 

Next Up.png

 

@shellydomo - SO glad we explored beast mode summary numbers this week!  

DataMaven
Breaking Down Silos - Building Bridges
Check out my story!
**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"

All Replies
Black Belt

Re: Next Up Summary #

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 Dataset

Then 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.

Major Brown Belt

Re: Next Up Summary #

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!  :->  

 

Next Up.png

 

@shellydomo - SO glad we explored beast mode summary numbers this week!  

DataMaven
Breaking Down Silos - Building Bridges
Check out my story!
**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"
Blue Belt

Re: Next Up Summary #

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 + Data
Major Brown Belt

Re: Next Up Summary #

@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
Check out my story!
**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"
Blue Belt

Re: Next Up Summary #

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)),'')

 

 

Capture.JPG

Broadway + Data
Dojo Admin

Re: Next Up Summary #

Absolutely! All done.

Dani

Dani aka "Mr.Dojo"

Dojo Admin
**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"
**You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.
Major Brown Belt

Re: Next Up Summary #

YAY!!!  Teamwork! 

DataMaven
Breaking Down Silos - Building Bridges
Check out my story!
**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"
Major Brown Belt

Re: Next Up Summary #

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
Check out my story!
**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"
Major Brown Belt

Re: Next Up Summary #

@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
Check out my story!
**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"
Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!