November 19, 2020
Workout Wednesday 2020 Week 47 Solution Guide - Clustered Histogram
bins
,
clustered histogram
,
floor
,
level of detail
,
LOD
,
tutorial
,
Workout Wednesday
,
WOW2020
No comments
First off, please don't follow this guide if you haven't already given the challenge your best effort. It won't benefit your learning to simply follow these steps without learning what you did right and wrong.
The toughest parts of this challenge is creating the x-axis. You can't use traditional bins because:
- You can't lump together the $2,000+ orders (bins are fixed sizes).
- You can't use bins in calculations, thus you can't lump them together this way either.
So how do you mimic what bins do without using bins? You could write some crazy long case statement with 20 options in it (from 100-2000 for each $100 bin), or you could simply use the FLOOR function. The FLOOR function rounds to the nearest integer of equal or less value. Think of it like a round down to the nearest integer.
Ok, so what does that have to do with bins? With a bit of math, the floor function allows us to mimic bins. For example, this calculation will create bins of 1,000:
FLOOR([Sales]/1000)*1000)
This obviously means, then, that to create bins of 100, we simple remove a zero:
FLOOR([Sales]/100)*100)
And now we're really close. The problem remaining is that it's looking at the row level, but we want each bins to be at the Order ID level. We don't have Order ID in the view, therefore we need to create a level of detail expression, I called it SALE AMOUNT:
FLOOR({ FIXED [Order ID] : SUM([Sales]) }/100)*100)
Calculations work inside out in Tableau. To turn this into a sentence, I would say:
- For each Order ID, return the sum of sales.
- Divided each Order ID sales by 100 and round down to the nearest integer.
- Multiply that result by 100 to get it back up to bins of 100.
Great, but what about lumping together all of those above $2,000? A couple of simple IF statements do the trick. I started by building the view like this:
Uh oh! We need to do something to make the bars next to each other instead of on top of each other. Let's create a calc that offsets the bars:
This will put the bars that are in the 0-100 bin at 25, 50, and 75. Then those in the 100-200 bin will be 125, 150, and 175. And so on.
We then need to go back to our SALE AMOUNT calculation and account for both the offset and lumping together above $2,000.
Ta da! Magic! Now that we have the correct calculations, it's a matter of building the view.
On the Size shelf, change it to Manual sizing and drag it around until the bars are split up a bit. The tooltip requirement involved a couple more calculations, but none of them are complicated. You can get them from my viz, but only do that if you're REALLY stuck. Give it your best effort first.
Click here or the image below for the interactive version and to download my solution.
Subscribe to:
Post Comments
(
Atom
)
No comments
Post a Comment