Tips and Techniques: GROUP and GROUPBY functions

These two functions typically work together. For example, the GROUP function is handy for returning one unique instance of a date. For example, the GROUPBYfunction is handy for returning a sum of values that align with each unique instance of a date.

But there are a few important things you need to know about these two functions.

  • One is that GROUP and GROUPBY sort as if the unique instances are text, i.e. alphabetically. For example, months might display as April, February, etc.
  • Two, if you have specified a condition in the “values” part of the GROUPBY formula, you MUST specify a condition in the “measures” part of the GROUPBY function.
  • By default, the GROUPBY function sums all values.

 Tip: A good rule of thumb is to evaluate all parts of a formula. In GROUPBY the "values" part of the function must return the same number of items as the "measures" part of the GROUPBY function. Otherwise, the results will be incorrect.

Let’s look at an interesting example from our Support Desk. In this example, the DATE CONVERT function is used to correct the sort order and the GROUPBY function totals all values using that same sort order.

The formula for the X: axis, formatted as: Text, is:

GROUP( A:A)

Returns a unique instance of the date.

But look it displays as: Apr-2014, Feb-2014, Jan-2014, and Mar-2014. To correct this, change the formula to sort by year and month (yyyyMM) using a function called DATE_CONVERT. Then wrap another DATE_CONVERT around the whole formula to change yyyyMM to Jan, Feb, Mar, Apr (MMM).

DATE_CONVERT( GROUP( DATE_CONVERT( A:A , “MMM-yyyy” , “yyyyMM” ) ) , “yyyyMM” , “MMM” )

Now, it reads correctly: Jan, Feb, Mar, April. You need to keep the DATE_CONVERT function as part of the Series formula. By default the GROUPBYmethod will sum all values in column B, e.g. Feb = 80.

GROUPBY( DATE_CONVERT( A:A , “MMM-yyyy” , “yyyyMM” ) , B:B , method )

Learn more:

Now that you know more about the GROUP and GROUPBY functions, check out the following tutorials:

Important concepts

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

Feedback and Knowledge Base