Setting conditions on axes

When creating a visualization, you select which columns to present on the various axes, and how to aggregate the data. What is more, you can determine in more detail what to display by specifying conditional expressions on the axes. That way, what is shown in the visualizations depends on the conditions.

One function you can use for conditions in an axis expression is If(Arg1, Arg2,Arg3). You interpret it as "if argument 1 is true, return argument 2 as the result, but if argument 1 is false, return argument 3".

For example, the expression If([Amount]<10,"yes","no") says that "if the value in the Amount column is lower than 10, the result of the expression is yes, but if the value is 10 or higher, the result is no".

Below are some examples of how you can apply conditional expressions in different visualizations.

Conditional expression applied on the value axis in a bar chart

The example data contains test results for a number of people. To pass the overall test, the summarized result from Test A and Test B must exceed 30. Assume you want to show how many men and how many women succeeded, and leave out those who did not pass.
Example data
As a start, the bar chart is created with one bar per gender.
Bar chart

Procedure

  1. Right-click the value axis selector to open the pop-up menu.
  2. Select Custom Expression.
  3. In the Custom Expression dialog, enter the expression Count(If(([Test A] + [Test B])>30,1,null)).
    The result of the If function is 1 for those who exceeded the total of 30. For those who did not pass, the Null argument indicates that no value should be returned.
    Then the frequency of 1 is counted, split per gender.
  4. Click OK.
    The value axis expression now shows how many passed the test.
    Bar chart with expression

Conditional expression applied on the color axis in a map chart

The example data contains a three-day temperature forecast for different cities. The markers in the map chart represent the average temperature for each city.
Map chart showing average temperatures
You may want to distinguish cities with an average temperature higher than 20° C by coloring their markers differently from the other markers. This can be done by applying a conditional expression on the color axis.

Procedure

  1. Right-click the color axis selector to open the pop-up menu.
  2. Select Custom Expression.
  3. In the Custom Expression dialog, enter the expression If(Avg([Temp])>=20,"Average 20 or higher","Average lower than 20 ").
  4. Click OK.
    The result of the color axis expression is a split into two categories, one category with cities fulfilling the condition, and one category that does not. The categories are colored differently.
    Map chart with condition on color axis