Grouping by month year orders my dates incorrectly

@richard.nolan Hi, I believe that the problem statement is concerned with ordering (or SORTING) of the data rather than how to prepare the line chart. If that's the case, I believe that following is one of the simplest ways of resolving the issue:

Set a common date value against each date value that fall in a given month and a given year. Let's take an example: For any value of 'date_reported' between 01/01/2016 and 31/01/2016, the default or common date should be 01/01/2016 or For any value of 'date_reported' between 01/12/2014 and 31/12/2014, the default or common date should be 01/12/2014. You may do it quiet easily by opting for the OOTB date time functions provided by the database vendor. Similar to 'month_year', the new values should be part of an additional column in the view (if you haven't had a view already, consider constructing one such, so that it holds an additional column which contains data based on the actual data) on which you will perform the GROUP BY and SORT operations. So your data finally look like below after adding the new column:

{
{ id:1, incident_type: "Critical",month_year: "Jan 2016", date_reported: "12/01/2016 09:30",date_reported_default_value:"01/01/2016"},
{ id:2, incident_type: "Critical",month_year: "Feb 2016", date_reported: "24/02/2016 09:30",date_reported_default_value:"01/02/2016"},
{ id:3, incident_type: "Minor",month_year: "Jan 2016", date_reported: "07/01/2016 09:30",date_reported_default_value:"01/01/2016"},
{ id:4, incident_type: "Critical",month_year: "Mar 2016", date_reported: "17/03/2016 09:30",date_reported_default_value:"01/03/2016"},
{ id:5, incident_type: "Major",month_year: "Feb 2016", date_reported: "12/02/2016 09:30",date_reported_default_value:"01/02/2016"}
}

So finally you will end up in having a common value for all the dates that fall in a given month and a given year. Now you may also ignore 'month_year' as our prime focus is on 'date_reported_default_value' and we would be performing GROUP BY and SORT operations on 'date_reported_default_value'.


So you may now see that the SORT problem is addressed. As the type of the data is 'Date', sorting can be done when you are invoking queryEntity(). And also please make a note that 'date_reported_default_value' should be used to perform GROUP BY and SORT later on.

When it comes to display, you may depend on fn!datetext() or fn!text() for formatting the values in 'date_reported_default_value' so that you can come up with the desired formatting as per your usecase. For instance an expression as discussed below, can be fed to "categories" to apply the formatting over the actual data.

Example:
load(
local!queryEntityData:a!queryEntity(
entity: cons!MY_ENTITY,
query: a!query(
aggregation: a!queryAggregation(
aggregationColumns: {
a!queryAggregationColumn(
field: "date_reported_default_value",
isGrouping: true
)
}
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: -1,
sort: a!sortInfo(
field: "date_reported_default_value",
ascending: true
)
)
)
),
a!lineChartField(
label: "Incidents per month",
categories: apply(fn!text(_,"mmm YYYY"),local!queryEntityData.data.date_reported_default_value)
)
)


You may also depend on the data of 'month_year' which you have already done, but this makes the simple operations tedious by leading us to expression rules which again formats the data and derives the results.

@richard.nolan Hi, I believe that the problem statement is concerned with ordering (or SORTING) of the data rather than how to prepare the line chart. If that's the case, I believe that following is one of the simplest ways of resolving the issue:

Set a common date value against each date value that fall in a given month and a given year. Let's take an example: For any value of 'date_reported' between 01/01/2016 and 31/01/2016, the default or common date should be 01/01/2016 or For any value of 'date_reported' between 01/12/2014 and 31/12/2014, the default or common date should be 01/12/2014. You may do it quiet easily by opting for the OOTB date time functions provided by the database vendor. Similar to 'month_year', the new values should be part of an additional column in the view (if you haven't had a view already, consider constructing one such, so that it holds an additional column which contains data based on the actual data) on which you will perform the GROUP BY and SORT operations. So your data finally look like below after adding the new column:

{
{ id:1, incident_type: "Critical",month_year: "Jan 2016", date_reported: "12/01/2016 09:30",date_reported_default_value:"01/01/2016"},
{ id:2, incident_type: "Critical",month_year: "Feb 2016", date_reported: "24/02/2016 09:30",date_reported_default_value:"01/02/2016"},
{ id:3, incident_type: "Minor",month_year: "Jan 2016", date_reported: "07/01/2016 09:30",date_reported_default_value:"01/01/2016"},
{ id:4, incident_type: "Critical",month_year: "Mar 2016", date_reported: "17/03/2016 09:30",date_reported_default_value:"01/03/2016"},
{ id:5, incident_type: "Major",month_year: "Feb 2016", date_reported: "12/02/2016 09:30",date_reported_default_value:"01/02/2016"}
}

So finally you will end up in having a common value for all the dates that fall in a given month and a given year. Now you may also ignore 'month_year' as our prime focus is on 'date_reported_default_value' and we would be performing GROUP BY and SORT operations on 'date_reported_default_value'.


So you may now see that the SORT problem is addressed. As the type of the data is 'Date', sorting can be done when you are invoking queryEntity(). And also please make a note that 'date_reported_default_value' should be used to perform GROUP BY and SORT later on.

When it comes to display, you may depend on fn!datetext() or fn!text() for formatting the values in 'date_reported_default_value' so that you can come up with the desired formatting as per your usecase. For instance an expression as discussed below, can be fed to "categories" to apply the formatting over the actual data.

Example:
load(
local!queryEntityData:a!queryEntity(
entity: cons!MY_ENTITY,
query: a!query(
aggregation: a!queryAggregation(
aggregationColumns: {
a!queryAggregationColumn(
field: "date_reported_default_value",
isGrouping: true
)
}
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: -1,
sort: a!sortInfo(
field: "date_reported_default_value",
ascending: true
)
)
)
),
a!lineChartField(
label: "Incidents per month",
categories: apply(fn!text(_,"mmm YYYY"),local!queryEntityData.data.date_reported_default_value)
)
)


You may also depend on the data of 'month_year' which you have already done, but this makes the simple operations tedious by leading us to expression rules which again formats the data and derives the results.