Community Tip - Learn all about PTC Community Badges. Engage with PTC and see how many you can earn! X
I have a dataTable that has columns that I would like to aggregate by the date part of the timestamp field but I'm not sure how to accomplish this.
There are some JavaScript functions you can use to get the Date only from a Date/Time and then you can use the build in Aggregate service.
you can use this:
var params = {
types: 'STRING' /* STRING */,
t: events /* INFOTABLE */,
columns: 'date' /* STRING */,
expressions: 'timestamp.getUTCFullYear()+"/"+(timestamp.getMonth() + 1)+"/"+timestamp.getUTCDate() '/* STRING */
};
// result: INFOTABLE
var r = Resources["InfoTableFunctions"].DeriveFields(params);
A few quick hints for folks who may not have used the infotable Aggregate function.
e.g. Use parameter entries like this:
columns: "name,Date" /* STRING */,
aggregates: "COUNT,COUNT" /* STRING */,
because something like this does not assume you want to use 'COUNT' on both columns and instead will throw an array out of bounds error:
columns: "name,Date" /* STRING */,
aggregates: "COUNT" /* STRING */,
Perhaps well known details to others, but I spent a while looking in the wrong direction today before I figured this out. Hope this save others similar frustration.
As an aside, using the 3 step process of (1) query your data, (2) derive a new field for 'Date', and (3) Aggregate seems to do the job well for getting daily totals to use on charts etc.