Sort by month in aggregate on a date attribute

Sort by month in aggregate on a date attribute

  

Hello, 

Sorry for the basic question but I can't quite see how to get it to work...

In my database table there is a date attribute (YYYY-MM-DD), I want to group the dates together so I can use the data in charts/graphs. 

I want them grouped by Year and Month so 2018-01, 2018-02, 2018-03...

I have tried to reformat the date using the FormatDateTime function but get the error that the function can't be executed in the database.

Is there a way of doing this correctly?

Thanks 

Peter

Solution

Hi Peter,

You can use the built-in Functions Month() and Year() to Group By on those.

EDIT: It would look something like this:

Solution

Hi Peter,


In your aggregate, you can add a computed attribute to get year + date and group by  this attribute.


Hope it helps.

João

Kilian Hekhuis wrote:

Hi Peter,

You can use the built-in Functions Month() and Year() to Group By on those.


Thank you, haven't used much of the date functions so didn't know they were available. 

Hi Peter,

They are. That is, the built-in Functions can be used when there are database equivalents, since they need to be executed by the database.