For week 16 of #WorkoutWednesday, Curtis tells us that his inspiration for the challenge came when he wanted to create the “Are you busy?” chart from Google in Tableau.

The challenge requires:

  • Create a histogram that shows the average number of daily orders for each day of the week.
  • When the user selects a date, a pink bar should be superimposed showing the number of orders for the selected day. Only this pink bar will show label and its description will be dynamic according to certain rules specified in the challenge.
  • Additionally, under the histogram, context information about the average orders must be presented for the day of the week that corresponds to the selected date.

In addition, Curtis included a special requirement: “Do not use any table calculations”.

Perfect to use LOD calculations! Who does not like LOD calculations?

Next, I share the steps that I followed.

Step 1: Create parameter “Selected Day”.

The parameter will be of type “Date” and it will be useful for the user to identify the day he wants to compare. The current value I established on 12/18/2018.

Step 2: Calculate the day of the week corresponding to each date and the day selected by the user.

That is, it’s required to know the day of the week (1-7 or by name of the day according to format)

Weekday =

DATEPART(‘weekday’, [Order Date])

Weekday of Selected Date =

DATEPART(‘weekday’, [Weekday of Selected Date])

In both variables use the custom format “dddd”.

Step 3: Calculate the variable “Average Daily Orders”

Average Daily Orders =

{FIXED [Weekday] : AVG(

{FIXED [Order Date] : COUNTD([Order ID])})}

The internal LOD calculation {FIXED [Order Date]: COUNTD ([Order ID])} allows us to obtain the number of orders for each day.

The external LOD calculation allows us to obtain the average value of the number of average orders for each “Weekday”.

Step 4: Calculate the variable “Orders by Selected Date”

Orders by Selected Date =

{FIXED [Weekday] : COUNTD(IIF([Order Date] = [Selected Date], [Order ID], NULL))}

The LOD calculation allows us to count only the orders of the day of the date selected by the user.

Step 5: Calculate the variable “Max Daily Orders”

Average Daily Orders =

{FIXED : MAX(

{FIXED [Order Date] : COUNTD([Order ID])})}

The internal LOD calculation {FIXED [Order Date]: COUNTD ([Order ID])} allows us to obtain the number of orders per day.

The external LOD calculation allows us to obtain the maximum value of the number of orders per day, in the entire data source.

Step 6: Create variable to help us with the labels for the pink bar.

Let’s remember that the challenge tells us that only the pink bar should show information and the text to be displayed will be dynamic according to a long, but long … ….   list of rules specified in the challenge.

Label1 =

IIF([Weekday] = [Weekday of Selected Date] AND [Orders by Selected Date] > 0, “Selected Date”, NULL)

Label2=

IIF([Weekday] = [Weekday of Selected Date] AND [Orders by Selected Date] > 0,

IIF([Orders by Selected Date] = [Max Daily Orders], “as busy as it gets”,

IIF([Orders by Selected Date] >= 0.8 * [Max Daily Orders], “very busy”,

IIF([Orders by Selected Date] >= 0.6 * [Max Daily Orders], “pretty busy”,

IIF([Orders by Selected Date] >= 0.4 * [Max Daily Orders], “busy”,

IIF([Orders by Selected Date] >= [Average Daily Orders], “busier than normal”,

“not busy”)))))

+ CHAR(10) +

STR([Orders by Selected Date]) +

IIF([Orders by Selected Date] > 1, ” orders”, ” order”), NULL)

Step 7: Create the main chart.

Two bar charts with synchronized double axis will be used.

Define the charts:

  • Columns ⬅   Weekday
  • Rows ⬅   “AVG(Average Daily Orders)” and “MAX(Orders by Selected Date)”
  • Color ⬅   “Color”
  • Tooltips ⬅   MAX(Max Daily Orders) for the main axis
  • Text ⬅   “Label1” and “Label2” on the secondary axis

Use bar in blue for the main axis and in pink with 45% transparency for the secondary axis.

In the main axis define a reference line with the variable “Max Daily Orders” and the label indicate that it is personalized with the following description “as busy as it gets”

Picture02Step 8: Create “Text” chart for the context information shown below the main chart

In this worksheet the filter of [Order Date] = [Selected Date] is applied

Define the chart:

  • Text ⬅   AVG(Average Daily Orders).
  • Text ⬅   ”Weekday of Selected Day”.

Picture03Ready!

Link to Tableau PublicPicture04After working with multiple challenges requesting “Do not use LOD Calculations”, it was comforting to see now the legend of “Do not use any table calculations”.

Thank you very much Curtis!

If you have any questions about the blog, do not hesitate to contact me on Twitter (@rosariogaunag)

Regards!

Rosario Gauna

 

Advertisements