0

Grouping and sorting a data source

Johannes Wali

January 24, 2019 11:00

None

Follow

Hi

I got a data source with campaign data. Every row represents data from one day for one campaign, e.g.:

Date            | Campaign      | Impressions
===============================
01/23/2019  | Campaign 1  | 1200
01/23/2019  | Campaign 2  | 80
01/24/2019  | Campaign 2  | 210

What I really need is the campaign impressions grouped by weeks to display it within different klips of my dashboard. With the example above it would something like:

Week            | Campaign      | Impressions
===============================
2019-04  | Campaign 1  | 1200
2019-04  | Campaign 2  | 290

I tried to create a modelled data source based on the original data source that groups the campaigns on a weekly basis but couldn't achieve it. That's what I tried so far:

1. Create a new column for a "grouping key" consisting of the week of year and campaign name (e.g. "2019-03-Campaign 1"):
   CONCAT(DATE_CONVERT(@Sheet1,A:A;,"dd-MMM-yy","YYYY-ww"),"-",@Sheet1,B:B;)
2. Group campaign column by this newly created key:
   GROUPBY( CONCAT(DATE_CONVERT(@Sheet1,A:A;,"dd-MMM-yy","YYYY-ww"),"-",@Sheet1,B:B;), @Sheet1,B:B;, "first(values)" )
3. Group impressions column by this newly created key:
   GROUPBY( CONCAT(DATE_CONVERT(@Sheet1,A:A;,"dd-MMM-yy","YYYY-ww"),"-",@Sheet1,B:B;), @Sheet1,C:C;, "sum(values)" )

 

Can someone help me with getting a modelled data source that groups campaigns by week of year? Perhaps there's another solution than a data source but I'd really like to re-use the data because I need it in multiple klips.

An additional question: can I sort a modelled data source by a specific column?
I didn't find any possibility so far...

Thanks a lot!

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Seema Nikam January 25, 2019 04:17

  Hi Johannes,

  You can do this by using applied actions. Before that just convert the display format of the date column to 'yyyy-ww' in the properties panel and set the aggregation to 'First'.
  
  

   

  And then apply 'Group' action on the campaign column. Now you will see the summed impressions by each campaign.

  
  

   

  Follow this link to know more about date and time formats: https://support.klipfolio.com/hc/en-us/articles/216182377-How-do-I-use-Date-Time-formats- 

   

  Are you looking to apply sort on a modelled datasource column within a klip? 

   

  -Seema

• 0
  
  Johannes Wali January 25, 2019 13:30

  Hi Seema

  Your solution works on a table component but I would be interested how you would do the grouping and sorting in a modeled data source. Is there a way to achieve the same?

  Thanks!

• 0
  
  Jeff Nelson June 13, 2019 18:05

  I am also trying to do this. Would like to be able to sort my modelled data source so the most recent data is on the top. 

• 0
  
  Hải Đăng Nguyễn July 31, 2019 04:14

  I want this feature too. Trying to create grouped data model instead of raw data from data source.