Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! 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.