I think you need to take two things into account:
1) Is real time data important? Because it does make a difference if the graph should give the current state or if the state of yesterday is good enough.
2) Maintainability. Should everyone be able to grasp how the data is gathered or is this a setup once and never to change action?
Making the data all in a microflow would be my first route. It will make the logic visible by every modeler. But it could be that by other means are faster. If you need near real time data this does matter.
And I would create special entities that hold the aggregated data. You could either refresh that data in a scheduled event or update the data by triggers in your model when the data actually changes.
To filter in a microflow by month or year I always create two dates. One at the first of the month and use addMonth to get the first day of the next month. Then you can filter the date by using somedate >= Date1 and < Date2. For years you can do the same and use the addYear function.
Unfortunately there is no way that I know of to insert a group by statement in an XPath query.
Data sets support OQL including group by, but they can only be used in reporting widgets (personally never used them).
Here are three options that you have (I would love to hear other ideas from people on the forum):
* use multiple database retrieves followed by an aggregate list, in each retrieve add a filter on the date attribute to only get the dates within a certain month. (addMonths is your friend)
* retrieve everything in a single query and then do the grouping yourself in a microflow or a java action. I would personally prefer a java action because Mendix has no equivalent to a HashMap)
* execute a custom OQL query in a java action
The last option makes me a bit anxious, but it is the same approach that the Excel export module utilizes.
Hope this helps,