SOLUTION TO THE CHALLENGE OF THE WEEK 10 OF #WORKOUTWEDNESDAY 2018 % of total , challenge , sunburst , table calc , LOD calc , Workout Wednesday

In Week 10 of Workout Wednesday, @LukeStanke had prepared a very special challenge for us: “KEEP AN EYE ON SALES”. Link to the Challenge

For me, who had never worked with a radial stacked chart, I think that just by reading the “spoiler” I blocked myself, I did feel that unpleasant feeling of not knowing how to start.

Luke gave us two formulas as a spoiler:

Radius * COS (2 * PI () * [Percent of Circle]) will return your Y-axis

Radius * SIN (2 * PI () * [Percent of Circle]) will return your X-axis

And after seeing the spoilers, I say OK, I already have the formulas and now the question is, what variables will I put there?

At the beginning, I searched through radial chart blogs, although very good, they were also blogs that were written for the use of multiple configuration parameters, which necessarily increased the complexity of the formulas used and increased my confusion.

I had to take a look at the work of @VizWizBI, to remove the blockade and I generated a first version.

After this first version, I wanted to give myself time to assimilate the challenge and with the help of @ebautistag, we tried with the experience gained, start the challenge of zero again. That is, try to understand the variables in the following graph. And then I will share the solution we got:

Step 0: Understand what variables we need for the formulas.

Percent of Circle: This variable is determined by:

• The number of the week plotted
• The total number of weeks
• The requirement that week 1 must be in the 12:00 position

Radius: This variable is determined by:

• The sales percentage of each segment
• The accumulated sales percentage of the previous segments.
• The spaces of 0.15 left between each segment
• And the minimum values ​​of 1 and maximum of 2.3 indicated by Luke.
• The difference of:
• Final range of 2.3, less
• Initial range of 1.0, less
• Two spaces of 0.15 between segments or 0.30 of total space
• It leaves us a value of 1, to distribute each segment based on its percentage of sales.

Step 1: For each segment line two points are required.

Therefore, it is necessary to create the union of the data table of Superstore with itself, this to have two “marks” for each segment-week. Why? Because the first “mark” is the point with the beginning of the line and the second “mark” is the end of the line that represents the % sales of the segment.

We use the union within Tableau, which creates a new field called “Table Name” with the values ​​”Orders” and “Orders1”.

Step 2: Calculate the “Percent of Circle” field. The position within the circle is calculated according to the week number to be plotted. As if it were a clock hand and you had to indicate the 52 points to stop.

Percent Circle = (DATEPART (‘week’, [Order Date]) – 1) / 52

Step 3: Calculate the Percentage of the Sales of each Segment. We use an LOD calculation to calculate sales by segment and divide it by total sales by excluding the segment.

% Sales by Segment =

{INCLUDE [Segment]: SUM ([Sales])} / {EXCLUDE [Segment]: SUM ([Sales])}

Step 4: Calculate the Percentage of the Sales of Accumulated until the Previous Segment. The accumulated percentage of sales per segment is calculated and the value of the current segment is subtracted. This is a “using [Segment]” table calculation

% Accumulated Previous =

RUNNING_SUM (SUM ([% Sales by Segment])) – SUM ([% Sales by Segment])

Step 5: Calculate Radius. In this calculation should be taken into consideration

 CONSIDERATION FORMULA The minimum value set by Luke of 1 1 + The spaces of 0.15 between each segment CASE ATTR([Segment]) WHEN “Corporate” THEN 0 WHEN “Consumer” THEN 1 ELSE 2 END * 0.15 The % accumulated previous sales + [% Accumulated Previous] If you are plotting the first or second points of each segment to accumulate or not the value of the segment sales percentage + IIF(ATTR([Table Name])=”Orders”,0,1) * SUM([% Sales by Segment])

Step 6: Calculate “x”

X = [Radius] * ATTR (SIN (2 * PI () * [Percent of Circle]))

Step 7: Calculate “y”

Y = [Radius] * ATTR (COS (2 * PI () * [Percent of Circle]))

Step 8: Move to the Shelf

• Detail –> the week -> DATEPART (‘week’, [Order Date])
• Color –> the variable “Segment”. And order the segments 1) Corporate, 2) Consumer and 3) Home Office

Step 9: Move the variable “x” to the column shelf and the variable “y” to the row shelf.

Apply to both variables the option of Calculation of Table using the option of “Compute Using” Segment.

Step 10: Select the chart of line and move to the shelf of Path -> the variable “Table Name”

Step 11: Just adjust colors and formats and filter or select data for the year 2017. Ready! 