We’ve made it through 12 weeks of #WorkoutWednesday2019 and we’ve yet to have a challenge that the overwhelming majority of the community has agreed was very difficult. And then, enter stage right, Luke Stanke with a candlestick challenge for week 13. He even said it in challenge intro, “This is very difficult.” And he even gave added an “advanced” portion of the challenge for those willing.

This week’s challenge took me a several hours over a few days to complete. But I made it through unscathed and now that I’m on the other side, I’ve been able to boil this challenge down and provide for you a blueprint for how to complete this challenge. Everything except the rounded bars, that is, since it was optional and because circles and be a misleading in this chart type.

Before we get started let’s take a look at what we’ll be building and the requirements. As always, here is the link

and here are the requirements

As Luke noted this is a difficult challenge. In fact, halfway through the challenge week, Luke tweeted this:

It appears this challenge is VERY difficult. A huge KUDOS to the people who've completed the challenge or have been persisting over the past few days. Thanks to all who are sharing partial or fully completed solutions! https://t.co/dJ1sgZDKLQ

— Luke Stanke ? (@lukestanke) March 28, 2019

So here we go, let’s dig in. We can see at the bottom that this dashboard is driven off of two parameters, let’s knock those out right now.

The first one is a date slider and includes a list of every date in our dataset.

You can see that I went ahead and defaulted it be set on the same date as Luke’s because if it’s really going to difficult, it’s very important that we start on level ground. Same goes the the “Range of Days” parameter

Now that we have these two parameters, we’re ready to start building. Now, I know that Luke said that the bottom full range overview is part of the advanced, but I found it a lot easier to build all my logic off that one first then build the dynamic logic in for the top chart. So we’ll start by building the full range chart.

We’ll start by dragging our date field to columns by continuous month

Now, again, Luke recommended unioning the data together but I went with the Gantt view because I find the final chart to be more exact because with circles, it could be misleading and easy to think that the value starts at the top of the top circle and ends at the bottom of the bottom circle. When the values are actually in the middle of each circle.

Going the Gantt route gives you more precision

Let’s talk about candlesticks for a minute. Candlesticks are used a lot in visualizing stocks to show four measures. Open, close, min and max. We can achieve this chart by overlaying two gantt charts on a dual axis. Before we start building, we’re gonna need need to some aggregating.

Keep in mind, everything we build will be used to visualize:

• Open
• Close
• High
• Low

Let’s start by adding [Low] to rows and changing the the aggregation to MIN(). This is saying, for each month this is the minimum [Low] value. Don’t forget to change the mark type to Gantt

Now to make a gantt we need to add a continuous measure on size. And that size is going to be the difference between the highest high and lowest low.

#Small Candle Size#
MAX( [High] ) – MIN( [Low] )

Looking good. But how can we validate this to be correct? Easy, let’s quickly add MAX( [High] ) to rows and flip to a dual axis and we should see the High bars at the top of the gantt bars

Yepper-do! Guess what? We’re halfway done with this chart! For this mark card, let’s set the color to gray and set the size to the smallest possible. Now for the gantt bars, we need to calculate the [Open] value for the beginning of the month and [Close] value for the last day of each month. Before I dive into those calcs I’m going to create take the ‘day’ part of each date.

#Day of Date#
DAY( [Date] )

#First Open#
IF [Day of Date] = {Fixed DATETRUNC( ‘month’, [Date] ) : MIN( [Day of Date] ) }

THEN [Open]

END

Okay, so what’s happening here? [Day of Date] is a discrete integer field that is the day of each date 1-31. So for each date, I’m only returning the [Open] field where it finds the lowest day value. You may be thinking, “couldn’t you just evaluate to find where day = 1 for each month?” Yep, I did that at first but didn’t take into account that our data is only Monday-Friday and even then we have some data missing, so we go with a LOD.

But what about the end of the month? I can’t just do 31 because I wouldn’t get anything for February and any other month that has less than 31 days. That calculation will require a simple LOD.

#Last Close#
IF [Day of Date] = {Fixed DATETRUNC( ‘month’, [Date] ) : MAX( [Day of Date] ) }

THEN [Close]

END

We can translate a fixed LOD into the following sentence structure. For each [combination of these fields] do this [aggregation]. So our LOD above says, “for each continuous month, take the largest day value.” Then we can evaluate that against each row and where ever it finds a match, it will return the close value for that row.

I highly recommend the practice of translating your calculations into a sentence.

Now before you start working ahead of me, don’t get all “too big for your boots” and think that you can make this gantt the same way we did that first. The reason it worked that way is because we were already dealing with highs and lows. This time we’re working with an open and close value and there’s order to which would be the lowest. But we can calculate it

#Big Candle Lowest#
MIN( SUM( [Last Close] ), SUM( [First Open] ) )

Yes, did you know that you can evaluate to values on the same row and return the smaller (or larger) number? Pretty cool, right? It wasn’t I started doing these #WorkoutWednesday challenges until I learned that. Now I use it quite often at work.

Now lets drag that rows as well.

Now for the size, we’ll calculate the difference between the [Last Close] and [First Open]

However, remember this will be used for the “size” or “length” of our gantt bars. So what happens when have a negative difference? We can’t have “negative length”, right? So we’ll take the absolute value of the difference.

#Big Candle Size#
ABS( SUM( [Last Close] ) – SUM( [First Open] )

Now, we can put this field onto size of our second axis

Looking good! Now, let’s add the color element; just a simple boolean calculation

#Big Candle Color#
SUM( [Last Close] ) – SUM( [First Open] ) <= 0

Add this to color of the secondary axis, edit the colors for True to be red and False to be black.

And now we’re ready to dual axis this sucker!

This is looking really good! Let’s keep rolling. Let’s start to use those parameters we created at the very beginning. They will be used to highlight what timeframe is being shown in the larger dynamic view above this.

To build this let’s go ahead and put a reference line on this sheet that shows the date we selected in our parameter

Okay, now this will be the middle of our window so we need use our range of days parameter, ADD half to our selected date and SUBTRACT half. So the first thing we need to do is calculate half our range

#Half of Range#
INT( { MIN( [Range] ) } / 2 )

Why am I using an LOD? The simple answer is aggregation. This will make more sense in just a bit so I’ll swing back. Don’t let me forget…

Now that we have this let’s build our MIN and MAX reference line calculations

#Min Ref Date#
DATE( DATEADD( ‘day‘, –[Half of Range], [Date Selector] ) ) 

#Max Ref Date#
DATE( DATEADD( ‘day‘, [Half of Range], [Date Selector] ) ) 

Okay, back to the LOD above. If I had done taken the minimum and divided by 2 it would’ve made that field an aggregated measure. And then Tableau would’ve required me to aggregate my dates in the ref calcs which would’ve resulted in aggregated date fields and those cannot be used for reference lines. I learned all this during this challenge and I pass this knowledge on to you! To better understand aggregate calculations, I highly recommend checking out this video by Zen Master Chris Love

And now that we have those reference dates, let’s add them to our sheet.

And now we can shade below the min and above the max the gray color leaving the analysis window white; lastly we’ll remove our date selected line altogether.

Perfect-o! I don’t know about you but I’m starting to feel pretty good about this challenge…but the more challenging part is still in front of us. Now let’s tackle, the dynamic portion. Let’s recap those dynamic requirements:

I feel like we need to create some “helper” fields first that simply evaluates the range of days value similar to [Half of Range] field.

#Convert Range to Months#
{ MIN( [Range] ) } / 12

#Convert Range to Weeks#
{ MIN( [Range] ) } / 7

Here’s what these calcs look like in a view

So now we’ll build on this to create a dimension that I’ll use to build my dynamic aggregations

#Date Level#
IF [Convert Range to Months] >= 20 then ‘month‘
ELSEIF [Convert Range to Weeks]<20 and [Convert Range to Weeks]>=15 then ‘week‘
ELSE ‘day‘
END

This field will be used change the aggregation of dates in our dynamic view. Now that we have this, we’re going to use the exact same logic we used for this our full range view with the date level added on.

In order to build out this sheet, we first need to great a dynamic date so check this out

#Dynamic Date#
DATE( CASE [Date Level]
WHEN ‘month’ then DATETRUNC( ‘month’, [Date] )
WHEN ‘week’ then DATETRUNC( ‘week’, [Date] )
ELSE [Date]
END)

Next we need to limit our view to only the date range in non-shaded area of our full range view.

#Date Range#
[Dynamic Date] >= [Min Date Ref]
AND
[Dynamic Date] <= [Max Date Ref]

We’ll put that the filter shelf and set to TRUE

Now, just like before we can setup primary axis (small candles) EXACTLY as we did our full range using the same fields.

Alright, here’s where things get fun. Remember above where found the first [Open] and last [Close] for each month? We need to do the same thing but it needs to flex depending on how the dates are aggregated. Here’s my solution:

#First Open_Dynamic#
IF [Date Level] = ‘month’ THEN
( IF [Day of Date] =
{ FIXED DATETRUNC(‘month’,[Date]):MIN([Day of date])}
THEN [Open] END )

ELSEIF [Date Level] = ‘week’ THEN
(IF DATEPART(‘weekday’,[Date]) =
{FIXED DATETRUNC(‘week’,[Date]):MIN(DATEPART(‘weekday’,[Date]))}
THEN [Open] )

ELSE [Open]
END

Now how’s that for a calculated field, huh? Oof! I’m sure there’s an easier way out there but this is where my head went and it works so…HOORAY! We already talked about the month section in a calculation in the overview and the ELSE portion is just the value but I wanted to focus on the ‘week’ portion of this calc to explain what it’s doing.

I actually struggled with this one for a bit. I even tweeted about my trials

Ooooh, @lukestanke has an #workoutwednesday dark side. I’m 99.5% done, I just can’t figure out how to calculate the first open and last close of any week that doesn’t start or end on Monday & Friday (respectively).

I’m not done yet, I’ll get there. pic.twitter.com/Nnui0Xslxd

— Sean Miller (@HipsterVizNinja) March 30, 2019

See…I do struggle with these challenges sometimes 😉

After many failed trials I consulted the trusty Tableau KB. And specifically I looked at Date Functions to look for a clue on how I could make this work. And then halfway down you’ll see the list of datepart values and one is weekday and that’s the key to making this work! Then I tweeted this as a follow up to the one above

And then I figured it out! Blog post coming! Great challenge, @lukestanke pic.twitter.com/PTJtHI7Prp

— Sean Miller (@HipsterVizNinja) March 30, 2019

And for the last [Close], simply duplicate that field and change the [Open]’s to [Close] and the min()’s to max() and you’ll be good go!

Everything from here on is duplication. You’ll need to duplicate the:

• Big Candle Lowest
• Big Candle Size
• and Big Candle Color

fields and update the calcs therein to reflect the dynamic view. Then build your chart just as you did before, create the tooltip, add the final formatting updates and…

YOU DID IT! Way to go! Click for the interactive version.

It was fun, it was challenging and we learned A TON!

Go forth and viz!