SORT

You can filter, group, aggregate and sort your visualization without the use of formulas. Using the More Actions menu allows you to apply these actions directly in your visualization. See the More Actions article to learn how to use these actions with Series, Axes, and Hidden Data.

SORT

SORT( values, type, return values )

The SORT function sorts values according to type:

  • ascending
  • ascending numeric
  • descending
  • descending numeric

If return values (optional) are provided, the results are returned in the same sorted order of values.

Examples

Use the following data source:

  A B
1 Fruit Units
2 Apples 10
3 Peaches 15
4 Lemons 20
5 Bananas 5
6 Oranges 12
7 Pears 17

This example sorts Units and returns them in ascending order.

SORT(SLICE(values), type)

  • values: set to Units data in column B (B:B).
  • type: set to ascending numeric.

Results: 5, 10, 12, 15, 17, 20

Note: SLICE is used to remove the column header.

Using the return values parameter

This example sorts the Units and returns the corresponding fruit names in ascending order.

SORT(SLICE(values), type, SLICE(returnvalues))

  • values: set to Units data in column B (B:B).
  • type: set to ascending numeric.
  • returnvalues: set to Fruit data in column A  (A:A).

Results: Bananas, Apples, Oranges, Peaches, Pears, Lemons

Note: The number of values listed in the return values parameter must be the same as the number of values listed in the values parameter.

Using SORT, GROUP, and GROUPBY in a bar chart

This example uses GROUPBY to group the amount of views according to the countries listed and SORT to return the data in descending numeric order to display in a bar chart.

Use the following data source:

  A B
1 Country Views
2 Canada 1900
3 United Kingdom 900
4 United States 1200
5 Germany 1500
6 Austrailia 1449
7 France 1320
8 Spain 999
9 India 1000
10 Canada 110
11 United States 121
12 United Kingdom 99
13 Canada 112
14 Spain 102
15 Germany 20
16 Canada 99

 

The formula below is used in the Series component of a Bar/Line chart to display the data.

SORT(GROUPBY(SLICE(values1),(SLICE(values2))),type)

  • values1: set to Country data in column A (A:A) and wrapped in SLICE to remove the column header.
  • values2: set to Views data in column B  (B:B).
  • type: set to descending numeric.

The formula below is used to display the country values on the X axis of the Bar/Line chart. The parameters are set to the same data as listed above, however, this formula uses GROUP to return the sorted values as country names. 

SORT(GROUPBY(SLICE(values1),(SLICE(values2))),type,GROUP(SLICE(returnvalues)))

  • returnvalues: set to Country data in column A (A:A).

 

Related links: