aggregation ?

Question

Hi all, 
 I'm having an issue with aggregation and i've not been able to find a good answer. I have a dataset that is made up off a number of collection of records. Something like id groupid value updatedOn 1 1 xx 19/01/2021 10:00 2 1 xx 19/01/2021 10:00 3 1 xx 19/01/2021 10:00 
 4 2 xx 10/01/2021 10:00 5 2 xx 10/01/2021 10:00 6 2 xx 01/01/2021 10:00 
 7 2 xx 01/02/2021 10:00 8 3 xx 01/02/2021 10:00 9 4 xx 01/02/2021 10:00 I need to display the total responses grouped by the groupid each month, and display in a line graph. something like Month GroupCount Jan 2 Feb 1 I was thinking aggregation but im not having much luck

Chris · Answer

The way I generally do this is by creating a view on the database side which breaks out the month and year into their own fields with something like MSSQL's DATEPART() function - then you have separate values to aggregate on, vs dealing with the datetime stamp in its entirety.

Mike Schmitt · Answer

I haven't tried this myself yet exactly, but I'd assume this is the sort of thing the newer "GroupingFunction" functionality in a!queryAggregationColumn() is for. Based on your provided column names, I'd expect maybe something like this might just work... and if not, it'd be useful to see what your exact code is, what your exact output is, and how it fails to meet your expected output. 
 a!queryAggregation(
 aggregationColumns: {
 a!queryAggregationColumn(
 field: "groupid",
 isGrouping: true()
 ),
 a!queryAggregationColumn(
 field: "updatedOn",
 isGrouping: true(),
 groupingFunction: "MONTH"
 ),
 a!queryAggregationColumn(
 field: "id",
 isGrouping: false(),
 aggregationFunction: "count",
 alias: "groupCount"
 )
 }
)