In this Tips and Techniques article, let’s take a closer look at the GROUPBY function, more specifically the method part of this important function.
You know the GROUPBY function will sum by default. You know it will count by using the expression: count(values). You know it will average even. But, what else can it do?
First, it is handy to know that method is a literal string and therefore, allows for a range of expressions. The expression will include the word values.
Why? Because values refer to what you have selected in the second part of the GROUPBY function, measure: the values you want to return. And, these values are associated with the first part of the GROUPBY function, values: the unique entries you are grouping the values by. For example, you might be returning a sum of values for a specific date.
Method is how the values are calculated. Method returns one value per unique grouping. Below are some other method expressions you might not know about.
||Returns the first value of a group of values. Usually in a measure field that is text.|
||Calculates the average of a set of values.|
||Joins values together.|
Tip: When using the "Insert literal string or number" button to type in these values, pay attention to case. For example, if I type
join(VALUES), my formula bar turns maroon, indicating this is a wrong formula.
Let’s look at an interesting example from our Support Desk.
In this example, in
JOIN (values) is used for method in a Table that is formatted as Mini-Chart: Spark Bar.
The formula for the Column labelled "Values" is:
GROUPBY (A:A, B:B, "join(values)")
You type in join(values) with the Insert literal string or number button. You do not need to type in the quotes as these are inserted automatically.
When evaluated the GROUPBY method joins together the values of each category of fruit, and then returns a quick visualization using the format of Mini-Chart: Spark Bar. For example: Oranges is 400, 450, and 500.
The data source used in this example is:
Now that you know more about the GROUPBY 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: