Next Up Summary #

RobynLinden

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!

BlueRooster

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.

DataMaven

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!  

BlueRooster

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.

DataMaven

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!  

RobynLinden

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. 

DataMaven

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

RobynLinden

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

 

 

[Deleted User]

Absolutely! All done.

Dani

DataMaven

YAY!!!  Teamwork! 

DataMaven

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

@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?