GROUP, GROUPBY, and COUNTDISTINCT (+video)

For grouping, aggregating, filtering and sorting of your data, try the group, filter and sort properties associated with your component.

What's in this article?

  • Learn how to use the GROUP, GROUPBY, and COUNTDISTINCT to create a Klip.
  • Step-by-step instructions.
  • Examples of scenarios where the functions are useful.

Introduction to GROUP and GROUPBY

Using GROUPBY with the Method parameter

GROUP, GROUPBY, COUNTDISTINCT

These functions are used to specify a series of unique elements to group together and remove duplicate values from your data. They are designed to complement each other to aggregate and simplify data.

  • GROUP allows you to remove duplicates from a column, such as when a column has multiple instances of the same value. 
  • GROUPBY collects values based on a specified aggregation method (like GROUP) so that the unique values align with a parallel column.
  • COUNTDISTINCT counts the number of each unique value in a column.

Note: The results for these functions are ordered alphabetically by list item.

You can download the data source used in this article here: Regions and units.

GROUP

In this example, the Regions in column A are grouped so duplicate Regions are not displayed.  

GROUP(SLICE(values))

values: set to the Regions data in column A (A:A), and wrapped in SLICE to remove the column header. 

GROUPBY

The Regions in column A are grouped and the Units in column B are grouped (summed unless specified otherwise) according to the Regions. 

GROUPBY(SLICE(values1), SLICE(values2))

  • values1: set to the Regions data in column A (A:A) and wrapped in SLICE to remove the column header. 
  • values2: set to the Units data in column B (B:B).

Optional steps

The default method is SUM(Values). You can set a different method by choosing one from the drop-down list or typing one in as a string.

GROUPBY(SLICE(values1), SLICE(values2),"method")

  • values1: set to the Regions data in column A (A:A).
  • values2: set to the Units data in column B (B:B).
  • method: set to "average(values)".

Hover over the image below to watch a short video.

COUNTDISTINCT

COUNTDISTINCT counts the number of each unique value in a column of data. In this example, COUNTDISTINCT returns the number of times a specific region was recorded in the data to reflect the times there was a sale in that region. SLICE is used to remove the column header from the data column.

COUNTDISTINCT(SLICE(values1))

  • values1: set to the Regions data in column A (A:A).

Using GROUPBY to create a spark line, spark bar, or win/loss chart

GROUPBY can be used to format your data in a comma separated list to use those values in a spark line, spark bar, or win/loss chart. This is useful for aggregating multiple unique elements in your data source and then plotting them using one of the mini chart formats.

GROUPBY(SLICE(values1), SLICE(values2),"method")

  • values1: set to the Regions data in column A (A:A).
  • values2: set to the Units data in column B (B:B).
  • method: set to "join(values)".

What's next?

Did you have trouble with the material in this article?

These articles may help: