datatable aggregate by day

[davidcor]

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. 

[paic]

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.

[LaurentGermain]

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);

[pschmaltz]

A few quick hints for folks who may not have used the infotable Aggregate function.

• the columns, aggregates, and groupByColumns parameters support comma separated list of values. BUT be sure to not include any white spaces along with those commas. (e.g. use groupByColumns: "name,Date", not groupByColumns: "name, Date"​).
• If you specify aggregating on multiple columns, be sure to include multiple aggregate types too.

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.