Select Page

While there is a detailed post on Shading between lines using LOD’s from DataSchool UK – Click Here, I tried to give a shot on this in three ways  – Table Calculation , Simple Calculated field and LOD.

# Option 1: Using Simple Calculated field

My Dummy Data Source contains 3- Column’s (Date by quarter, GDP and Public Debt)

The Following Steps are relevant for all the options with this data source

• Move the Quarter to Columns Shelf
• Change the Quarter to “Exact Date”
• Add the Quarter to filter Shelf and filter by Date Range from 1991 to 2020 (For demonstrations only)
• Move the measure values to row shelf.
• Move the Measure name to filter and select only “GDP”, “Public Debt” and the Calculated field created to find Minimum Value per year.
• Change the Marks to Area
• In Analysis menu, click on “Stack Marks”  and select only “Off”

The theory behind shading between lines is finding the minimum value between two measures/two dimensions for the given date and coloring the area formed to match with worksheet color. (Jargons – finding the Inner join Area between the two Values)

To find the minimum value between the two measures in the connected data source the following formula to be used.

IF MEASURE1 > MEASURE2 then MEASURE1 ELSE MEASURE2 END

Drag the Calculated field “Min Value Per Year” to the Measure Values, and Measure names to color shelf, change the color of “Min Value per year” to match that of work sheeet shading.

# Option 2: Using Table Calculation

The Table calulation and LOD need a dimension against the values to compute. Hence, I had Pivoted the above data source by pivoting GDP and Debt column in the Data source. (In the Data source sheet, Highlight two column’s and right click to select the Pivot option)

Now, go to new sheet and do the following

• Drag Quarter to columns shelf
• Drag Values to Row shelf
• Description to the Marks card and change it to “color”
• Other formattings – same as option 1.

To find the minimum value between the dimension for the given date(Quarter) by table calulcation we need to use “Window_Min” formula in the calculated field and select the order of calcualtion in the table calculation option.

To explain above calculation in simple English, I would say – for un-ticked dimension “Quarter”, calculate minimum value in ticked dimension “Description”.

Once the calculated field with above formula is created , drag it to measure value and change the order of table calculation.

Finally, change the color of area formed using minimum value to the white as to match the shading of Worksheet.

# Options 3 : Using LOD (Level of Detail)

As mentioned at the beginning of the blog, there is a detailed explanation on this topic from DataSchool UK (Please check the above for the link)

For the purpose of this demonstration I will be using FIXED LOD function to find the Minimum value within dimension. The Syntax for this as below:

{FIXED [DIMENSION]AGGREGATE([MEASURE])}

{FIXED [Quarter] : MIN([Value])}

The steps after this follows similar to the option 2:

# Dual Axis Tweaking

After Creating the shading between the lines, the overall visualization can be further enhanced by creating the outline of the area using dual axis method.

• Drag the Values to the ROW shelf in addition to the Measure Values
• Change the chart ype of SUM(Values) to Line.
• Remove Measure names from the Color in the  -SUM(values) Marks Shelf
• Right click on the SUM(values)  and select Dual Axis
• Right click on the axis label and select “Synchronize axis”
• Change the shades in Area Marks to lighter than the Line chart, so the line clearly stands out of area chart after Synchronize.

Feel free to post any suggestions or improvements, I generously accept complements , if you do have one, please drop below.

##### Author: Shiva Ravi

Shiva, a graduate in Electrical and Electronics Engineering, had an exciting Oil Industry career for over a decade exploring ocean bed through marine seismology. Being an Instrumentation specialist in International waters, he found his passion for data visualization while working with binary seismic data. Recently, in a short stint as a Technical Asset Manager, he discovered Tableau to manage the ridiculously diverse scientific equipment in the Global marine warehouse. He refers to inventory planning as finding a needle in the haystack, and Data analytics is like having a wand in hand. In a quest for learning, Shiva found Data school, which paved the way for the most-awaited career switch as Data Analyst. He is currently pursuing Masters of AI (Part-time) from Western Sydney University, and in his spare time, you can spot him cycling, running or designing logos. Recently, he tried his hand in cooking and took an interest in exploring cuisines. If there is something he cannot live without, that would be coffee!