Grouping and sorting a data source


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"):
  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!


  • 0
    Seema Nikam

    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? 



  • 0
    Johannes Wali

    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?


  • 0
    Jeff Nelson

    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

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

Please sign in to leave a comment.