GROUP and GROUPBY functions

What's in this article?

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

Online course on dashboard functions: GROUP and GROUPBY
Webinar: Using GROUP and GROUPBY

Introduction to the GROUP and GROUPBY functions
Using the GROUPBY function with the Method parameter

How to use the GROUP and GROUPBY functions

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

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

To use the GROUP function

Note: These procedures assume you are in the Klip Editor as a result of building a Klip or editing a Klip.

  1. Select a component from the Component Tree (Column, Primary Value, etc).
  2. Click the Start formula link in the formula editor.
  3. Click the Insert Function button on the formula editing toolbar.
  4. Select Data Manipulation>GROUP from the Function's menu.


  1. Click on the values link in the GROUP function.
  2. Select a range of values to group together.

Tip! Use the SLICE function to select an entire column of data and remove the column header from the data selection. 


To use the GROUPBY function 

Note: These procedures assume you are in the Klip Editor as a result of building a Klip or editing a Klip. 

  1. Select a component from the Component Tree (Column, Primary Value, etc).
  2. Click the Start formula link in the formula editor.
  3. Click the Insert Function button on the formula editing toolbar.
  4. Select Data Manipulation>GROUPBY from the Function's menu.

  1. Click on the values link in the GROUPBY function.
  2. Select a range of values to group together.
Tip! Use the SLICE function to select an entire column of data and remove the column header from the data selection.  
  1. Click on the measure link in the GROUPBY function.
  2. Select a range of values to merge together. Note that measure must be the same size (same number of items) as values.

Note: The default method for measure is SUM(Values). You can change the method by modifying the measure link in the GROUPBY function. 


Optional steps – Changing the method of measurement in the GROUPBY function

  1. Click on the method link in the GROUPBY function.
  2. Click the Insert literal string or number button on the formula editing toolbar.
  3. Type Average(values) into the text field.
  4. Click the Apply button.

Note: This works for any number of math functions like MIN, MAX, or MEDIAN. Remember to type the method manually using the format outlined in step 11.

The average of the aggregate values is displayed.


Examples of the GROUP/GROUPBY functions

The GROUP and GROUPBY functions are useful for aggregating data in a data source with multiple data points. These examples all use nested formulas to achieve the desired result.

Using the GROUPBY method to count data points for a unique element

The GROUPBY function can be used to count the number of data points for a unique element in your data source, similar to the COUNT function. Using the GROUPBY will aggregate the number of data points for each element and display them in your Klip as a series. This example uses a SLICE function to remove the column header from the data source.


Using the CONCAT and GROUPBY functions to create a spark line, spark bar, or win/loss chart

The GROUPBY function can be used with a CONCAT function 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. This example uses the SLICE function to remove column headers from the data source. Also, note that you will need to manually insert a comma in between each data set to create the comma separated list.

Tip! To save time, you can copy and paste formula elements in the formula editor. To do this, click on the element, press CTRL+C, and then click on the next value and press CTRL+V.

Using the GROUPBY, DATE, DATEVALUE, and IF functions

This example is designed for a specific use-case where you are working with multiple entries for each date in your data source. For instance, you are tracking events on a pass/fail basis, and the values are linked to a specific time of day.


To aggregate these values according to the date of entry (rather than the time of entry), you will use the GROUPBY, DATE, DATE VALUE, and IF functions. Here are the basic steps to help you accomplish this.

  1. Insert a GROUPBY function.
  2. Click the values link in the GROUPBY function.
  3. Insert a DATEVALUE function.

  1. Click the dates link in the DATEVALUE function.
  2. Insert a DATE function.
  3. Select the date values from the data source.
  4. Click the format link.
  5. Use the free text option to insert an appropriate date format (d/M/yyyy).

Tip! Check out this resource to learn more about date formats.

  1. Select the DATEVALUE function by clicking on its title.
  2. Click the format link in the DATEVALUE.
  3. Using the free text option to insert an appropriate date format (d/M/yyyy).

Tip! Check out this resource to learn more about date formats.


  1. Click the measure link in the GROUPBY function.
  2. Insert an IF function.
  3. Click the condition link.
  4. Insert a parenthesis.
  5. Create a condition that is appropriate for your data.
  6. Click the if true link.
  7. Using the free text option to insert a numerical value (eg. "1").
  8. Click the if false link.
  9. Using the free text option insert a numerical value (eg. "0">.

What's next?

Did you have trouble with the material in this article?

These articles may help:

Functions

  1. Introduction: Using formulas
  2. Data Manipulation functions
  3. Logic functions
  4. Math functions
  5. SELECT function
  6. Text Functions
  7. Date and Time Functions
  8. Statistics Functions
  9. ABS function
  10. AND function
  11. ARRAY function
  12. AVERAGE function
  13. BETWEEN function
  14. BLANK function
  15. CAPITALIZE function
  16. CEILING function
  17. CONCAT function
  18. CONTAINS function
  19. COUNT function
  20. COUNTALL function
  21. COUNTBLANK function
  22. COUNTDISTINCT function
  23. COUNTIF function
  24. COUNT_DAYS function
  25. COUNTNUMERIC function
  26. CUMULATIVE function
  27. CUMULATIVE_DIFFERENCE function
  28. DATASOURCE function
  29. DATE and DATEVALUE functions
  30. Using Date and Time Formats
  31. DATERANGE function
  32. DATE_ADD function
  33. DATE_CLOSEST function
  34. DATE_CONVERT function
  35. DATE_ENDOF function
  36. DATE_IN function
  37. DATE_SET function
  38. DATE_STARTOF function
  39. DATE_UNITVALUE function
  40. FLOOR function
  41. ERF function
  42. FIRST function
  43. ERFC function
  44. GROUP and GROUPBY functions
  45. IF function
  46. IN function
  47. INDEXOF function
  48. JOIN function
  49. LAST function
  50. LASTINDEXOF function
  51. LEFT function
  52. LOOKUP function
  53. LOWER function
  54. MAP function
  55. MAX function
  56. MIN function
  57. MEDIAN function
  58. MOD function
  59. MODE function
  60. Moving Average (Cumulative)
  61. Moving Average (Exponential)
  62. Moving Average (Simple)
  63. NORMSDIST function
  64. NOT function
  65. NOW function
  66. NUMBERFORMAT function
  67. OR function
  68. PADVALUES function
  69. POWER function
  70. RANK function
  71. REMOVE_EMOJI function
  72. REPEAT function
  73. REPLACE function
  74. REVERSE function
  75. RIGHT function
  76. ROUND function
  77. SET function
  78. SLICE function
  79. SLOPE function
  80. SORT function
  81. SPLICE function
  82. STANDARDIZE function
  83. STDEV function
  84. STDEVP function
  85. SUBSTITUTE function
  86. SUBSTITUTE_REGEX function
  87. SUBSTRING function
  88. SUM function
  89. SUMIF function
  90. SWITCH function
  91. TEXT_REVERSE function
  92. TODAY function
  93. TRIM function
  94. TRUNCATE function
  95. UPPER function
  96. URLDECODE function
  97. URLENCODE function
  98. VARIANCE function
  99. VARIANCEP function
  100. YESTERDAY function
  101. ZTEST function

Feedback and Knowledge Base