Pivot table for civi data - example is Month created v Source

This came up as a question for a civi report that could provide a pivot table for contacts created each month v the Source field. Having done most of these bits for other use cases I wanted to confirm this would work here.

This View uses Aggregation feature in Views under Advanced and the Pivot Table module.

To get Aggregation working for Months the recipe I used was 

  • Set Date field to M Y - and Aggregation for that field to Minimum
  • Set Sort Criteria for Date field to 'granularity = monthly'

Outcome is something like the image. I haven't ascertained why the first column has a total which differs from the value in the column when all other columns do the expected. The totals are the correct value, cell A1 should be showing 6. Weird

 

Note 1: View contains 3 displays, a block as List so you can eyeball data, a page with Pivot, and a page with just an aggregated table. To keep the data secure the pages are set to viewable only by Admin

Note 2: you need to be Admin usually to be able to Import a View

Note 3: if there are any other validation errors it is probably worth just overriding at the stage of importing it (i just spotted a date format that may be uncommon)