Body: <p>In Civi + Drupal latest, we have a number of campaigns that last for a year or more. We know their start and end dates, but don't particularly want to manually change their status to show whether they are 'In progress' or not. Start and end dates are sufficient for that for us.</p>
  
  <p>We'd like to create a chart or at least a View (or maybe a Report?) that shows the number of campaigns currently active in any particular month. </p>
  
  <p>I tried CiviVisualise but in 5.21 get a database error that says its installation failed. </p>
  
  <p>So... any ideas of how to create a View that aggregates the active campaigns in any particular month? I am thinking I might be missing something very obvious here, so grateful for any thinking that can put me right. From such a View we can then create a Chart using Drupal Charts.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-01-28T16:03:54.957Z
Comments.Text: I'm not sure you can do this as you would need to get a query that takes a month as an input and returns the campaigns (or number of them) in that month; then you'd need that query to run for a specified list of months to generate your dataset. I don't think this can be done with views, though it's fairly easy to do in code, but then you'd still need a way to present it - e.g. a expose it as a Drupal block using chart.
Comments.UserDisplayName: artfulrobot
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-05-05T22:21:18.870Z
Comments.Text: Thanks! I guess we'll keep thinking...
Comments.UserDisplayName: Andyg8
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-05-07T18:39:48.983Z
Comments.Text: Assuming that there are Activities associated with the Campaigns, you can create a view of Activities associated with the Campaigns, and aggregate by Campaign and time period, I believe.
Comments.UserDisplayName: Joe McLaughlin
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-10-05T17:35:53.290Z
Comments.Text: I opened a proposal to add a new feature for SearchKit to handle a generic "entity active period" which I think can help approximate a solution to this question. See [SearchKit: Add ability to search and filter by active periods](https://lab.civicrm.org/dev/core/-/issues/3878)
Comments.UserDisplayName: Francesc Bassas i Bullich
Tags: <drupal-views><civicampaign><chart>
Title: Create a report or graph of the number of campaigns active in any particular month
ThreadText: # Create a report or graph of the number of campaigns active in any particular month
  
  (Asked by: username_0 on 2020-01-27)
  
  In Civi + Drupal latest, we have a number of campaigns that last for a year or more. We know their start and end dates, but don't particularly want to manually change their status to show whether they are 'In progress' or not. Start and end dates are sufficient for that for us.
  
  We'd like to create a chart or at least a View (or maybe a Report?) that shows the number of campaigns currently active in any particular month.
  
  I tried CiviVisualise but in 5.21 get a database error that says its installation failed.
  
  So... any ideas of how to create a View that aggregates the active campaigns in any particular month? I am thinking I might be missing something very obvious here, so grateful for any thinking that can put me right. From such a View we can then create a Chart using Drupal Charts.
  
  **Tags:** drupal-views, civicampaign, chart
  
  > **Comments:**
  > **username_1**: I'm not sure you can do this as you would need to get a query that takes a month as an input and returns the campaigns (or number of them) in that month; then you'd need that query to run for a specified list of months to generate your dataset. I don't think this can be done with views, though it's fairly easy to do in code, but then you'd still need a way to present it - e.g. a expose it as a Drupal block using chart.  
  > **username_0**: Thanks! I guess we'll keep thinking...  
  > **username_2**: Assuming that there are Activities associated with the Campaigns, you can create a view of Activities associated with the Campaigns, and aggregate by Campaign and time period, I believe.  
  > **username_3**: I opened a proposal to add a new feature for SearchKit to handle a generic "entity active period" which I think can help approximate a solution to this question. See [SearchKit: Add ability to search and filter by active periods](https://lab.civicrm.org/dev/core/-/issues/3878)  
  
  ---
  
  ## 0 answers
  
  