Selecting values by using an expression on filter shelves
The Enter/Edit Expression interfaced may be used on a filter shelf to fine-tune value filtering, and to incorporate information about values from multiple rows of the dataset. To demonstrate this approach, let's use the example from building Cross tabulation, where we specify several discrete years of the dataset World Life Expectancy.
- On the Filters shelf, click Down Arrow on the year field placed there earlier.
Click [ ] Enter/Edit Expression
In the Enter/Edit Expression modal window, build an
We used the following expression to specify a range of year values:
[year]BETWEEN 1950 AND 2010
- Click Validate Expression.
- Click Save.
After clicking Refresh Visual, the cross tabulation
Note the range of years we specified in the previous step, and that the columns stop reporting life expectancy when it reaches the threshold of 50 years for all seven countries in Middle Africa UN sub-region.
Let us change the expression to report only when life expectancy is at 50 years
or more for the same year range.
([year]BETWEEN 1950 AND 2010) AND ([life_expectancy]>=50)
Note the following results:
- The first row is for year 1960, the first time a country in Middle Africa reported a life expectancy over 50 years.
- Only five out of seven countries are reporting; this means that life expectancy for Chad and Congo never reaches 50 years over the period covered by our dataset, through the year 2010.