Grouping of data for charting

Hi, I am using database connector (MySql) to query and collect the data. I have list of rows that has an attribute(column) that is date. Is there any simple way to group this data based on this date attribute (into years and months) and get counts like Excel does? I am using Anychart to do bar graph ( Years/month – x axis, #(count) of rows(corresponding to the year/month)- y axis).   Thanks, Manyam
1 answers

I would add a year attribute and a month enum attribute so you can then use those in XPath retrieves. A simple microflow could fill those values for you. The command formatDateTime($yourDate, ‘yyyy’) gives you the year and formatDateTime($yourDate, ‘MMMM’) gives you the month. Do note that you need the UTC versions depending on the use of localization or not.
You can use parseInteger to change the year text to an integer and use some string comparisons for the month to change it in a enum. You can then use an XPath like [year = 2020 and month = ‘January’] to retrieve all the objecst of that month and that year. Use the count list function to count the objects. You could store those counts in seperate entities if you like so you can use that entity for your graphs.