0

Selecting the first record of a group (GROUP BY)

Bart Jonk

August 30, 2019 09:40

None

Follow

I've got a table of measurements that are collected weekly, in which measurements could be missing or late.

id	name	date	measurement	period
1	customer 1	Jun 1, 2017	8875	rolling half year 1
1	customer 1	Jun 8, 2017	8908	rolling half year 1
1	customer 1	Jun 16, 2017	8934	rolling half year 1
1	customer 1	Jun 22, 2017	8958	rolling half year 1
1	customer 1	Jun 29, 2017	8996	rolling half year 1
1	customer 1	Jul 6, 2017	9018	rolling half year 2
1	customer 1	Jul 13, 2017	9046	rolling half year 2
1	customer 1	Jul 20, 2017	9065	rolling half year 2
1	customer 1	Jul 27, 2017	9097	rolling half year 2
2	customer 2	Jun 2, 2017	532	rolling half year 1
2	customer 2	Jun 8, 2017	541	rolling half year 1
2	customer 2	Jun 15, 2017	548	rolling half year 1
2	customer 2	Jun 22, 2017	556	rolling half year 1
2	customer 2	Jun 29, 2017	559	rolling half year 1
2	customer 2	Jul 7, 2017	562	rolling half year 2
2	customer 2	Jul 13, 2017	567	rolling half year 2
2	customer 2	Jul 20, 2017	569	rolling half year 2
2	customer 2	Jul 27, 2017	586	rolling half year 2

 

Is there a way to select all first values of the "rolling half year 2" ?

 

 

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Bart Jonk August 30, 2019 13:58

  Solved using this article: https://www.klipfolio.com/blog/most-important-formula-in-klipfolio

   

   

• 0
  
  Janice Janczyn September 03, 2019 14:14

  Hi Bart,

  Happy to hear you solved the question yourself.  Here's another approach that uses Actions instead of functions & formulas.

  If you want to group measurements by name, selecting only those in the rolling half year 2 period, you could for example build a table with a hidden data column.

       Column 1 - points to the 'name' column in your data
       Column 2 - points to 'measurement'
       Hidden Data - points to 'period'

  Then do the following steps:

  1. Click the 3 dots menu to the right of Hidden Data (in the component hierarchy), select Filter and set the Condition to Match 'rolling half year 2' (exclude the quotes).
  2. Click the 3 dots menu to the right of Column 1 and select Group. All other columns in the table are automatically aggregated, now you need to set the Aggregation method for the other column.
  3. Click the 3 dots menu to the right of Column 2 and set Aggregation > First.

  I hope this is helpful!

  Thanks,
          Janice