# Week 10 Solution

Below is a chart built for #WorkoutWednesday. It seems to have thrown people off a bit.

Just as a reminder here are the requirements:

• Use sales for 2017.
• The size of the dashboard 500px x 600px.
• If you think of the segments as a clock, Week 1 should be at the 12:00 position.
• The weeks should be evenly spaced based on the week number.
• You’ll be using geometry. The minimum radius is 1. The maximum radius is 2.3. (Spoilers provide some math help.)
• The space between the segments is .15.
• Note the tooltips to get some assistance on your math.

Before we tackle the circle, lets just create a line chart that emulates a bar chart.

### Step 1: Provide total control over each line thru a union

It’s important to consider one foundational principle when building any visualization in Tableau: you will only show aggregate information based on the number of dimensions you have on a view â€“ unless you use a level-of-detail calculation. The chart above shows sales by segment and week of the year. Week of year and segment are considered dimensions by Tableau and therefore if we create a view we should have one mark for every combination. And if we create that data we’ll see it’s exactly what we produce. Basically a dot plot.

But we really want a line. and a line is made up of two points. With Tableau its impossible to have two marks per combination on a view. In order to “hack” this line I’m going to use a trick and union the data to itself (Note: there are other ways this can be done but it’s just the way I prefer because I can keep the data balanced as every data point is duplicated twice).

After we union the data to itself. There is a new field in the data called [Table Name] this distinguishes the data sources from each other. My [Table Name] dimension has two members “Order” and “Order\$” — yours is likely “sales.csv” and “sales.csv1”.

With this data now duplicated, we can control the start point of a line by using logic statement with [Table Name]. In this case we’re going to build a base calculation that will help us along the way

#### [Data Level]

`[Table Name] = "Order"`

Again this is going to basically allow us to control each mark twice — one for each dataset.

### Step 2: Build base calculations

When doing analyses like this I like to use level of detail calculations because I don’t have to really think about aggregations based on the marks on the view, they are just aggregated to the level I am working with. Since I know I’m working with week and segment data I’m going to build a few level-of-detail calculations.

#### [Consumer Sales / Week]

```{FIXED DATETRUNC("week", [Order Date]) :
SUM(IF [Segment] = "Consumer" THEN [Sales] END)
}```

#### [Corporate Sales / Week]

```{FIXED DATETRUNC("week", [Order Date]) :
SUM(IF [Segment] = "Corporate" THEN [Sales] END)
}```

#### [Home Office Sales / Week]

```{FIXED DATETRUNC("week", [Order Date]) :
SUM(IF [Segment] = "Home Office" THEN [Sales] END)
}```

#### [Sales / Week]

```{FIXED DATETRUNC("week", [Order Date]) :
SUM([Sales])
}```

* This actually produces totals twice the value expected because we unioned the data to itself, but will be fine for this analysis since we are doing a percent of total. *

Each of these calculations are going to be very useful as we built out the chart above.

### Step 3: Emulate a stacked bar chart

Building this chart is a lot like building a stacked bar chart that shows percent of totals.

Consider how we will build each part of a stacked bar chart for:

Corporate

• start: 0
• end: [total % of corporate]

Consumer

• start: [total % of corporate]
• end: [total % of corporate] + [total % of consumer]

Home Office

• start: [total % of corporate] + [total % of consumer]
• end: 1

We con control the start and end points of each part of these lines using the [Data Level] function we just put together. When [Data Level] is true, let’s say that’s the start of the line and when [Data Level] is false lets say that’s the end of the line. The function would then look like this

#### Line Location

```IF [Data Level]           // Returns TF
THEN CASE [Segment]       // For true create start of each line
WHEN "Corporate" THEN 0
WHEN "Consumer" THEN [Corporate Sales / Week]/[Sales / Week]
WHEN "Home Office" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week]
END
ELSE CASE [Segment]       // For false create end of each line
WHEN "Corporate" THEN [Corporate Sales / Week]/[Sales / Week]
WHEN "Consumer" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week]
THEN "Home Office" THEN 1
END
END
```

Let’s create a line chart to see what this produces.

Add week of [Order Date] to Columns. Add [Line Location] to rows and make it a continuous dimension. Add [Segment] to color, change the mark type to line, add [Table Name] to path, and also add week of [Order Date] to detail. Voila! A line chart that looks like a bar chart!

Let’s get the spacing to work out. Edit your [Line Location] calculation. We’re going to make it easy and hard code the spacing in.

#### Line Location

```IF [Data Level]           // Returns TF
THEN CASE [Segment]       // For true create start of each line
WHEN "Corporate" THEN 0
WHEN "Consumer" THEN [Corporate Sales / Week]/[Sales / Week]+ .15
WHEN "Home Office" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week]+ .30
END
ELSE CASE [Segment]       // For false create end of each line
WHEN "Corporate" THEN [Corporate Sales / Week]/[Sales / Week]
WHEN "Consumer" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week]+ .15
THEN "Home Office" THEN 1+ .30
END
END
```

This hard-coding adds a nice space to our chart.

Believe it or not you are almost there for the whole thing!

## Step 4: Unit Circle time!

They key to all of this is understanding geometry from your youth, particularly the formula for a circle:

[radius] * sin(2Ï€ * [% of circle]) and/or [radius] * sin(2Ï€ * [% of circle])

I’ll give you a hint, we just built our radius sans one tiny thing, so all we need is to figure out the % of the circle. And the percent of the circle is just the percent of year completed. Remember that we want basically Week 1 to be equal to zero based on the requirements. so:

#### Percent of Year

```// This returns the percent of the year completed for each week
( DATEPART("week", [Order Date]) - 1 )
/
{FIXED YEAR([Order Date]) : MAX(DATEPART("week", [Order Date]))}
```

Now we can quickly build our [x] and [y] calculations:

#### x

```[Line Location] * SIN(2*PI() * [Percent of Year])
```

#### y

```[Line Location] * COS(2*PI() * [Percent of Year])
```

Now let’s build a view. Add [x] to columns and [y] rows. Make sure they are both continuous dimensions. Change the mark type to line. Add [Segment] to color, [Table Name] to Path, and week of [Order Date] to Detail. Add [Order Date] to filter and select just the year of 2017.

That produces this view:

You are actually so very close. The problem is the start point or our radius — [Line Location] — we want the minimum value to be 1 and the maximum value to be 2.3. Our current minimum and maximum values are 0 and 1.3. You can tell this by going back and looking at the last line chart we created where [Order Date] was on the columns shelf.

All we need to do is edit our existing [Data Level] calculation and add +1 right at the beginning.

#### Line Location

```1 +
IF [Data Level]           // Returns TF
THEN CASE [Segment]       // For true create start of each line
WHEN "Corporate" THEN 0
WHEN "Consumer" THEN [Corporate Sales / Week]/[Sales / Week] + .15
WHEN "Home Office" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week] + .30
END
ELSE CASE [Segment]       // For false create end of each line
WHEN "Corporate" THEN [Corporate Sales / Week]/[Sales / Week]
WHEN "Consumer" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week] + .15
THEN "Home Office" THEN 1 + .30
END
END
```

This changes our chart to:

Now with a little formatting we’ll have the chart we’re looking for:

### 9 thoughts on “Week 10 Solution”

1. Hey Luke, you have explained it fabulously. It’s an amazing workout and really a lot of thought process is involved.

Could you please check the “Line Location” calculated field as it’s not working for me. I used it as follows:

IF [[Data Level]]]
THEN CASE [Segment]
WHEN “Corporate” THEN 0
WHEN “Consumer” THEN [Corporate Sales/Week]/[Sales/Week] +0.15
WHEN “Home Office” THEN ([Corporate Sales/Week] + [Consumer Sales/Week])/[Sales/Week] +0.30
END
THEN CASE [Segment]
WHEN “Corporate” THEN [Corporate Sales/Week]/[Sales/Week]
WHEN “Consumer” THEN ([Corporate Sales/Week] + [Consumer Sales/Week])/[Sales/Week] +0.15
WHEN “Home Office” THEN 1 +0.30
END
END

and it keeps throwing me an error: “Expected END to match IF at Character 0”

I tried to change the False Condition by putting it in Else Clause but that didn’t work out for me as well.

Please have a look.

1. This is the correct line location calculation.

1 +
IF [Data Level] // Returns TF
THEN CASE [Segment] // For true create start of each line
WHEN “Corporate” THEN 0
WHEN “Consumer” THEN [Corporate Sales / Week]/[Sales / Week]+ .15
WHEN “Home Office” THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week]+ .30
END
ELSE CASE [Segment] // For false create end of each line
WHEN “Corporate” THEN [Corporate Sales / Week]/[Sales / Week]
WHEN “Consumer” THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week]+ .15
WHEN “Home Office” THEN 1+ .30
END
END

2. Steven Watson

Wow Luke, great tutorial! I’m not going to pretend I would have figured all of that out myself, but you stepping through it made sense and gave me a new tool. Thanks so much!

3. Awesome blog Luke…clear & concise explanations that made it so even I could recreate this ðŸ™‚

Really like Kyle’s Grizzlies adaption, too.

Really need to review my geometry…

@nick612hayden

4. What is the reason I got dots instead of lines in my graph? The dots do not connect together.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top