How To Color Band Conditionally

This post describes a fascinating procedure on how to create different color bands conditionally in a line chart with two reference lines.

Scenario:  In a line chart, there're two reference lines, one for the entire period and another for the last twelve months.  The requirement is to color the bands between the two reference lines by different colors based on:

      a)  If average sales for last 12 months is less than average sales for entire period (sales is decreasing), color the band as red.

      b)  If average sales for last 12 months is greater than average sales for entire period (sales is increasing), color the band as green.


Procedure:  To calculate sales for last 12 months, create this formula:

@Sales (last 12 months)
if attr(DATEDIFF('month', [Order Date] , {MAX([Order Date])} )) < 12 then Sum([Sales]) END

Then put this formula in the Detail pane and create the second reference line.

But then how do you color the band conditionally based on the difference of two average sales?

First, create formulas for average sales and average sales last 12 months.

@avg

window_avg(sum([Sales]))

@avg last 12 mths
window_avg([Sales (last 12 mths)])

Next, create a formula to return True/False based on the condition how to change color of the band.

@color T/F
[avg] > [avg last 12 mths]

Then, create four separate formulas in order to build two sets of reference lines, one set for red and another set for green.

Red reference lines are used for decreasing sales (when average sales for last 12 months is less than average sales for entire period):

@avg last 12 mths (less)
IF [color T/F] then [avg last 12 mths] END

@avg (more)
IF [color T/F] then [avg] END

Green reference lines for increasing sales (when average sales for last 12 months is more than average sales for entire period):

@avg last 12 mths (more)
IF NOT [color T/F] then [avg last 12 mths] END

@avg (less)
IF NOT [color T/F] then [avg] END

Put these four formulas along with formula @color T/F  in the Detail pane.  Change the five formulas to compute Pane (across) in order to calculate per each city.

To color the band conditionally, right-click on Sales axis and select ‘Add Reference Line.’  To color the band as red when average sales for last 12 months is less than average sales for entire period, select ‘Band’ for reference band.  Under ‘Band From’, choose ‘avg (more)’ for ‘Value’.  Under ‘Band To’, choose ‘avg last 12 mths (less)’ for ‘Value’.   Under ‘Formatting’, pick a red color for ‘Fill’.

To color the band as green when average sales for last 12 months is more than average sales for entire period, select ‘Band’ for reference band.  Under ‘Band From’, choose ‘avg (less)’ for ‘Value’.  Under ‘Band To’, choose ‘avg last 12 mths (more)’ for ‘Value.  Under ‘Formatting’, pick a green color for ‘Fill’.

The benefit of conditional color band is that it highlights which cities have increasing or decreasing sales during last 12 months.  And the impact is that users are visually notified what cities to pay attention to, especially if sales is decreasing.

Happy Planet Index

The 2016 Happy Planet Index (HPI), released by the New Economic Foundation in London, measures how well nations are doing at achieving long, happy, and sustainable lives.   Using ecological footprint as one of its measures, HPI highlights that it's possible to achieve high life expectancy & wellbeing without consuming too much resources from the Earth.

As a result, countries with high GDP that consume too much energy and have high ecological footprint might rank lower than countries with lower GDP and smaller ecological footprint.  For 2016, Costa Rica ranks first with the highest HPI.   Then, Mexico, Colombia, Vanuatu, and Vietnam round out the top five.

The visualization shows the country rankings throughout the world according to 2016 HPI.  Costa Rita ranks first, followed by Mexico, Columbia, Vanuatu, and Vietnam.  The ‘HPI Rank Changed (2012 vs. 2016)‘ section is a quadrant analysis that compares the HPI country ranking changes between 2012 and 2016.  Syria is the biggest decliner whereas Uruguay, biggest improver.

Discrimination in Online Ad Delivery

Have you been arrested?  Imagine this question appears when someone searches for your name on the Internet.  The Discrimination in Online Ad Delivery report by Professor Latanya Sweeney from Harvard University indicated that an Internet search by black-identifying names was more likely to yield an advertisement suggestive of an arrest record than a search by white-identifying names (download report here).  This racially biased advertisement distribution could inadvertently impact a person’s reputation and the affected person might not know about the potential negative consequences. 

However, advertisers are protected under the First Amendment of the Constitution that advertisements are commercial free speech.  But what if the advertisements suggestive of a criminal background appear more frequently for one racial group than for another’s, is that still free speech or has it become racial discrimination?  As this research yielded more questions than answers, perhaps Internet search companies should yield to moral high grounds and refine their search logic to provide a user experience free of racial bias.  

This viz was influenced by the New York Times’ “Why Is Her Paycheck Smaller” graph, which has been well-received in the data visualization community for its elegant use of annotation to deliver the thought-provoking message.  In order to draw the diagonal lines in the scatter plot, I sought help from Tableau Zen Master extraordinaire, Joe Mako.  Then I inserted the ‘% Arrest Ads’ annotations to the viz.  The final result highlights the unfair issue of discrimination in online ad delivery.

In addition, the box plots to the right show the differential on the arrest ads display when searching for names according to race and gender.  

Learn About Tableau

This viz was made possible with the permission of Professor Latanya Sweeney from Harvard University and the contribution of Joe Mako, Tableau Zen Master.  I'm grateful to both for their generosity.

State of the Future

The 2013-14 State of the Future is a compelling global thematic report produced by the Millennium Project based in Washington, D.C.  Researchers from around the world collaborate to collect and provide a diversity of opinions, then distill and integrate those data to forecast the future.  It’s a thought-provoking read into the advancements and challenges of the humanity and what the future might hold for our 7.1 billion people living together in this interconnected world.

For such a special report, the graphs are quite rudimentary (download the 2013-14 State of the Future report here).  There’s such a wealth of information in this 243-page report that can yield many different visualizations.  I think this report can benefit tremendously from a team of data visualization experts who can produce beautiful and meaningful graphs befitting this fascinating report.  For my part, I’ve visualized the different indicators that together compose the State of the Future Index (page 5).

The data for this viz has both breadth (25 indicators) and depth (215 countries and from 1972-2013), however, the data is not uniform because it is not available for all the years and for all countries.   So there’s a small hiccup in the viz when changing from some indicators to another.

For example, data for indicator ‘Life expectancy at birth (years)’ ranges from 1972-2012.  But for indicator ‘Internet users (per 100 people)’, data is available only from 1990-2012.  So when the indicator is changed from ‘Life expectancy’ to ‘Internet users’, the world map might disappear.

The solution is to scroll to the next available year and the world map will reappear.

Since this report is for futurists, I’ve used the Forecast function in the ‘World’ graph to predict the next 6-years trend for the indicators using 95% prediction intervals.  In addition, the ‘World’ graph shows how the world population is doing for each indicator.  There're more winnings than losings so we are making progress over the years, albeit at the expense of the environment!

Learn About Tableau

As an optimist, I believe the future is bright and promising ahead.

Nigeria

In April 2014, Nigeria surpassed South Africa as having the biggest economy in Africa.  Nigeria’s Gross Domestic Product (GDP) was adjusted to $510 billion in 2013, 89% larger than previously estimated.  This was because the economists adjusted how the country’s GDP was calculated, something they hadn’t done since 1990.

GDP adjustment is the process of replacing an old base year with a more recent one which reflects the dynamic price structure and captures economic growth. The IMF standard for GDP adjustment is every 5 years.  But in Nigeria’s case, it took 24 years.

What had changed in the last 24 years was Nigeria’s fast economic expansion, changing from a financial base of crude oil to more diversified activity including such vibrant sectors as manufacturing, agriculture, financial services, mobile telephony and Nollywood.  For example, the number of mobile phone subscribers has leapt from a few hundred thousand customers to some 120M today.  The telecoms sector has jumped from less than 1% to almost 9% of GDP.  Nollywood, which did not appear in 1990, is said to contribute 1.4% to the GDP.  More small and medium scale enterprises (SMEs) have also been flourished. ¹

Now that Nigeria’s GDP is larger than United Arab Emirates’ and South Africa becomes Africa’s Number Two, Nigeria is expected to continue its 7% average annual growth, while South Africa’s economy would chug along with 2% growth for next couple of years.

The reality is that Nigeria still has many problems to overcome such as corruption, underdeveloped infrastructure, & large poverty.  However, Nigeria is a young market and the opportunity is huge.  "If you're not in Nigeria, you're not in Africa," said Nigerian Finance Minister Ngozi Okonjo-Iweala.

_________________________

Note:

¹ Mordi, Frederick, “Nigeria overtakes rival South Africa”, African Business, June 2014, pp. 74-75

Learn About Tableau

This viz was made up of basic bar and line charts, with a touch of color to make it pop.

2014 World's Strongest Banks

The inspiration for this post came from this intricate Scattering Points in Parallel Coordinates (SPPC) design from Peking University.  One look and I was immediately captivated by its sophisticated complexity.  Joe Mako has also visualized similar multidimensional data with parallel coordinates chart using Tableau.

For this visualization of the world's strongest banks, 8 dimensions were used to form parallel coordinates.  For dimensions Overall Score, Non-Performing Assets to Total Assets, and Efficiency (Costs to Revenues), the lower the value, the higher the ranking.  Whereas for dimensions Tier 1 Capital ratio, Loan-Loss Reserves to Non-Performing Assets, and Deposits to Funding, the higher the value, the higher the ranking.

Let’s interpret the chart (this visualization would benefit more with a larger data population but Bloomberg Markets only released top 20 out of 97 banks ranked).  Asian and European banks account for about 40% each of the top 20 banks, and North American banks, 20%.  In general, Asian and European banks rank higher than North American banks.  The top ranking is Hang Seng bank from Hong Kong, followed by Desjardins Group from Canada and Norinchukin Bank from Japan, both tied at rank #2.

 

For Tier 1 Capital Ratio, European banks generally have higher ratio, which means they are relatively more well-capitalized than Asian and North American banks.

For Deposits to Funding, European banks have lower ratio, which means they tend to lend more and are less liquid than Asian and American banks.  On another hand, Asian and American banks have higher ratio, which means they lend less and are more liquid.  This might also mean that Asian and American banks might not earn as much as they can compared to European banks.

For Costs to Revenues ratio, Asian banks have lower ratio, which means that they are run more efficiently than European and American banks.

Learn About Tableau