In this article, let’s look at the SELECT function. This is a useful function to know because it returns data in so many different ways.
Simply said, the SELECT function allows you to return data based on a condition, or multiple conditions.
The condition you specify could also be a variable. A variable allows a user to display a subset of data based on a choice the user selects from a drop down list, or even by selecting a date.
When you create the condition, you can select from any number of different Logic functions to return the data you want to display.
- Returns data that matches only one condition i.e. Vancouver.
SELECT( C:C , ( B:B = “Vancouver” ) )
- Returns data that is equal to one of two conditions, i.e. either Vancouver or Ottawa.
SELECT( C:C , OR( ( B:B = “Vancouver” ) , ( B:B = “Ottawa” ) ) )
- Returns data that must match both conditions, i.e. equal to Vancouver and 10.
SELECT( A:A , AND ( ( B:B = “Vancouver” ) , ( C:C = “10” ) ) )
- Returns data that is between 10 and 20.
SELECT( B:B , BETWEEN ( C:C , 10 , 20 ) )
- Returns all countries except Germany.
SELECT( B:B , NOT( ( A:A = “Germany”) ) )
- Returns all countries containing Can.
SELECT( B:B , CONTAINS ( A:A , “Can” ) )
- Returns all cities in Canada.
SELECT( B:B , IN( A:A , “Canada” ) )
Tip: You don’t necessarily have to use the SLICE function with the SELECT function as the row or column header will most likely not equal the condition.
Let’s look at a simple example from our Support Desk.
In this example, the SELECT function returns the values in Column C depending on whether Column B is 1 or 2.
The formula for the X: Axis is:
GROUP ( A:A )
Result: Returns a unique instance of the date.
The formulas for the Series are:
SELECT( C:C , ( B:B = 1 ) )
Result: Returns the values in C where B equals 1.
SELECT( C:C , ( B:B = 2 ) )
Result: Returns the values in C where B equals 2.
What is also interesting about this Klip is that a property on the Y:Axis called Custom Origin is set to 20. This means that if a value is below 20, it will display below the origin line, and if a value is above 20, it will display above the origin line. Cool, eh!
You can also stagger labels on the X:Axis, if you want.
This Klip uses the following data source.
Now that you know more about the SELECT function, check out the following tutorials:
- Tips and Techniques - Function examples
- Tips and Techniques - Task examples
- Tips and Techniques - Google Analytics examples
These articles provide an overview of important concepts for building Klips: