Grouping by month year orders my dates incorrectly

...016", date_reported: "12/02/2016 09:30"}
}

Any thoughts on how to line graph this data?

Not sure if I get your requirement correctly, but it seems that you are looking forward to create a line chart to display Incidents per month. If that is the case then, you can use a below code snippet and feed the code actual values to generate a line chart.

=a!lineChartField(
label: "Incidents per month",
categories: {"Jan 2016", "Feb 2016", "March 2016"},
series: {
a!chartSeries(label: "Critical", data: {2,5}),
          a!chartSeries(label: "Major", data: {5,9}),
          a!chartSeries(label: "Minor", data: {7,10})
          },
xAxisTitle: "Month",
yAxisTitle: "Count",
yAxisMax: 100,
showLegend: true
)

As per my understanding you have to sort using paging info on date and time which will return data in order which you need.

@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.

sikhivahans - that's what I was thinking as well but what would the query aggregations look like to accumulate the count of Critical incidents, Marjor incidents, Minor incidents per month?

Thanks for commenting everyone, I always learn a ton of great new ways to work with Appian from you!

@richard.nolan To the best of my knowledge, there isn't a direct way of doing it. Attached an example of how to do it and with the code comments as well and I believe this should give you an idea.

Please do let us know in case if you have any questions.

Just to close the loop, what I wound up doing was creating a new field (as per your suggestion) that just contained the first day of the month for each record.

Then created an expression rule containing a queryEntity that retrieves the incident type as a parameter and groups based on the date field you've outlined. Works perfectly!

@richard.nolan Great..! That's correct if you want to see a total count of incidents(irrespective of type) per month.

The code snippet attached by me at last should aid you just in case if you further want to check the count per incident type in each month.

Cheers, I did look through the code and it makes sense, I'm going to play with it a little over the weekend, but I saw where you were going with it. Makes good sense!