Body: <p>I'm building a Drupal 7 View in which I want to aggregate contribution records for the year, grouping by month. But View's native aggregation option happens after the query is generated, so it's not possible to group by MONTH(receive_date). I tried using the Views Date Format SQL module, which in theory should let me apply the group by on the query, but it doesn't seem to handle non-core-Drupal date fields.</p>
  <p>I also explored using Views Aggregator Plus module, which does let me render a table with correct grouping. But my end goal is to display a chart (using the Charts module), so I need to be able to build the aggregate query without an alternate display format (you can't use VAP and Charts together -- they are mutually exclusive display format options).</p>
  <p>I feel like the ability to use Views to generate aggregate stats on Civi data is probably something people are doing... but I'm getting stuck.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-03-03T18:17:52.167Z
Comments.Text: from memory grouping by eg month involved some Contextual filters - i have found a d7 view doing this based on Content. I suspect the equivalent filters do not exist for civi but haven't looked. i can send the view over if it is worth your time to look at how it works
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-03-03T21:29:08.250Z
Comments.Text: I have not tried this, but I think it can be done. My theory is: Create your own sql query with aggregate built into the query, Then use this query for your view that uses the chart module.      My initial Google search, looks like you need SQL Views https://www.drupal.org/project/sql_views to use your own query. Then you can use  hook_views_data to get it in your View with Charts module. https://www.drupal.org/docs/7/api/database-api/sql-views
Comments.UserDisplayName: Iowa Boy
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-03-03T21:29:13.737Z
Comments.Text: If you are green at writing a sql query, create the view as you did with Aggregator Plus module. In Views > Settings under Live Preview you can check the box for "Show SQL Query" Then you can copy the query from the preview.
Comments.UserDisplayName: Iowa Boy
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-03-03T22:28:48.290Z
Comments.Text: I ended up creating a MySQL view and registering it using the views_data hook, which allowed it to be exposed to Drupal Views.
Comments.UserDisplayName: lcdservices
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-03-04T01:08:57.457Z
Comments.Text: Cool, Glad it worked
Comments.UserDisplayName: Iowa Boy
Tags: <drupal><drupal-views>
Title: Is it possible to group by a date in a Drupal view?
ThreadText: # Is it possible to group by a date in a Drupal view?
  
  (Asked by: username_0 on 2021-03-03)
  
  I'm building a Drupal 7 View in which I want to aggregate contribution records for the year, grouping by month. But View's native aggregation option happens after the query is generated, so it's not possible to group by MONTH(receive\_date). I tried using the Views Date Format SQL module, which in theory should let me apply the group by on the query, but it doesn't seem to handle non-core-Drupal date fields.
  
  I also explored using Views Aggregator Plus module, which does let me render a table with correct grouping. But my end goal is to display a chart (using the Charts module), so I need to be able to build the aggregate query without an alternate display format (you can't use VAP and Charts together -- they are mutually exclusive display format options).
  
  I feel like the ability to use Views to generate aggregate stats on Civi data is probably something people are doing... but I'm getting stuck.
  
  **Tags:** drupal, drupal-views
  
  > **Comments:**
  > **username_1**: from memory grouping by eg month involved some Contextual filters - i have found a d7 view doing this based on Content. I suspect the equivalent filters do not exist for civi but haven't looked. i can send the view over if it is worth your time to look at how it works  
  > **username_2**: I have not tried this, but I think it can be done. My theory is: Create your own sql query with aggregate built into the query, Then use this query for your view that uses the chart module.      My initial Google search, looks like you need SQL Views https://www.drupal.org/project/sql_views to use your own query. Then you can use  hook_views_data to get it in your View with Charts module. https://www.drupal.org/docs/7/api/database-api/sql-views  
  > **username_2**: If you are green at writing a sql query, create the view as you did with Aggregator Plus module. In Views > Settings under Live Preview you can check the box for "Show SQL Query" Then you can copy the query from the preview.  
  > **username_0**: I ended up creating a MySQL view and registering it using the views_data hook, which allowed it to be exposed to Drupal Views.  
  > **username_2**: Cool, Glad it worked  
  
  ---
  
  ## 0 answers
  
  