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]+ .15WHEN "Home Office" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week]+ .30END 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]+ .15THEN "Home Office" THEN 1+ .30END 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:

AnuHi Rody,

Thanks for the solution! I followed the steps and recreated the viz:

https://public.tableau.com/views/WeeklySalesTrendbySegmentfor2017/Dashboard1?:embed=y&:display_count=yes

Anu

Rahul VeerwalHey 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.

Kyle G.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

Luke StankeWhoops! Thanks for the fix!

SaritaGreat article. So easy to follow ..Thank you.

Steven WatsonWow 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!

Kyle G.Hi Luke,

I really enjoyed this week and love how easy this can be utilized for other data sets. I had to try it out on some sports data I had.

https://public.tableau.com/profile/kyle.greenough#!/vizhome/MontanaGrizzliesFootball/MontanaGrizzliesFootball

Thanks again!

Kyle G.

NickAwesome 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