2016 Arab Opinion Index

The 2016 Arab Opinion Index, published by the Arab Center for Research & Policy Studies in Doha, Qatar, is a public opinion survey across 12 Arab countries in the Middle East and North Africa.   The report provides insights into what Arab people think about democracy, economy, politics, religion, & ISIL.

This visualization presents what Arab people think about democracy.   In general, people have a positive opinion about democracy and more than 70% think it is a better form of government.   However, less than half of the respondents think their own country is ready for democracy.  Let's hope the people of these countries will have the governments with some forms of democracy as they desire.

Sheet Swapping for Bar Chart

This short & neat post describes an interesting solution on how to do sheet swapping for bar chart.

Scenario:  Sheet swapping doesn’t work well with bar chart. One of the worksheets doesn’t automatically reduce in size when the parameter value is not selected.

Fortunately, there’s a work-around solution for this problem.

Solution:  Insert a ‘NULL’ calculated field in the Rows pane of both worksheets. This should fix the problem and sheet swapping can be used with bar charts again.

Unauthorized Immigration

Dealing with unauthorized immigration is one of the key policy issues in the Trump administration, which has prioritized that unauthorized residents are not welcome in the US.  This policy has two main components (1) stopping illegal border crossing by building a wall and (2) deporting unauthorized residents already in the US who have committed any crimes.

This visualization analyzes and provides a composite picture of the unauthorized immigration.  The issue is emotionally complicated but let’s hope that the new administration will find the right balance between law enforcement and compassion for outsiders soon.

World Giving Index

Let’s start 2017 with an inspiring topic of philanthropy.  Every year, the London-based Charities Aid Foundation publishes the global-thematic World Giving Index report, which tracks the generosity of the countries’ inhabitants around the world.  The three giving behaviors used to measure the inhabitants' generosity are (1) Donate money to a good cause, (2) Help a stranger, and (3) Volunteer time.

Each giving behavior yields a score that counts the percentage of the country's inhabitants who participated in that behavior.  Then the World Giving Index is a combination of the scores of the three giving behaviors.  For 2016, Myanmar, the United States, and Australia are the top three most generous countries according to the World Giving Index.  Here’s to many more unconditional and happy givings.  Cheers!

Season's Greetings

How To Use Filter To Remove Nulls From Calculated Field

This short & neat post describes an interesting solution on how to use filter to remove null values from a calculated field.

Scenario:  Users would like to have an option to filter null values from a calculated field. 

Error:  To filter null values, the calculated field is put in the Rows pane, converted to discrete, and selected ‘Show Filter’.  However, if ‘Null’ is de-selected from the filter, an error message ‘invalid filter predicate’ will appear.

Fortunately, there’s a work-around solution for this bug (which is noticed in Tableau Desktop 9.2 and 10).

Solution:  Put calculated field in Rows pane, change to Discrete, & select 'Show Filter'.  Change filter to 'Exclude Values'.  In the filter, select Null to exclude null values.

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.