Looking for the same thing myself now. Needed to create a graph showomg two dataserie for date and NrOfSomething, and for date and NrOfSomethingElse, all grouped by date, which may have several objects per day.
DateTime NrOfSomething NrOfSomethingElse
20220918T01:01:01 5 1
20220918T22:22:22 4 1
20220919T21:00:00 2 2
20220919T22:22:22 1 2
Should give two lines having two dots:
- 20220918 value 9 and 20220919 value 3.
- 20220918 value 2 and 20220919 value 4.
Solved this by creating a microflow that:
Endresult is a list of objects that I can pass to a lineChart that will sum the values having the same date:
Potential Solutions:
Use something like the following query and format the column labels yourself (perhaps formatDateTime(parseDateTime(toString($currentObject/Day) + '-' + toString($currentObject/Month), 'dd-MM') , 'd, MMM') if you need to display the month as a string):
SELECT
COUNT(*) AS Total,
DATEPART ( DAY , Attribute ) AS DayFromAttribute,
DATEPART ( MONTH , Attribute ) AS MonthFromAttribute
FROM
MyFirstModule.Entity
GROUP BY
DATEPART ( DAY , Attribute ), DATEPART ( MONTH , Attribute )
Option if you must get a date type back, but this presumably has performance penalties:
SELECT
COUNT(*) AS Total,
DATEPART ( DayOfYear , Attribute ) AS DayFromAttribute,
MIN( Attribute ) AS Date
FROM
MyFirstModule.Entity
GROUP BY
DATEPART ( DAYOfYear , Attribute )
Other options involving something like the query above but joined with a select distinct of the datepart