3 Replies Latest reply on Dec 15, 2015 5:14 PM by petersch RSS
    davidcor Apprentice

    datatable aggregate by day

    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. 

      • datatable aggregate by day
        paic Collaborator

        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.

         

          • Re: datatable aggregate by day
            laurentger Apprentice

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

            • Re: datatable aggregate by day
              petersch Apprentice

              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.