Tips and Techniques: SELECT function

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.

Learn more:

Now that you know more about the SELECT function, check out the following tutorials:

Important concepts

These articles provide an overview of important concepts for building Klips:

Feedback and Knowledge Base