Aggregate Data from a Data Store Entity on a Date or Date and Time Field

Goal

Aggregate data from a data store entity, specifically the total number of employees by date, to display in a grid and chart.

This recipe uses an employee data structure and objects created through the Use the Write to Data Store Entity Smart Service Function on an Interface recipe. Make sure that recipes has been built first in order to see data in this recipe.

Grid Expression

Copy and paste the following grid expression into your interface to see a grid that groups data by year and month. Notice the name of the month is obtained using expressions and it is internationalized.

aggregate_data_on_date_grid

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
=load(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 20,
    /*Sort by year and month */
    sort: {
      a!sortInfo(
        field: "startDate_year",
        ascending: true
      ),
      a!sortInfo(
        field: "startDate_month",
        ascending: true
      ),
    }
  ),
  with(
    local!datasubset: a!queryEntity(
      entity: cons!EMPLOYEE_ENTITY,
      query: a!query(
        logicalExpression: a!queryLogicalExpression(
          operator: "AND",
          filters: {
            /* Remove null field values */
            /* for 'startDate'          */
            a!queryFilter(
              field: "startDate",
              operator: "not null"
            ),
            a!queryFilter(
              field: "startDate",
              operator: "between",
              value: {
                todate("1/1/2016"),
                todate("12/31/2018")
              }
            )            
          }
        ),
        aggregation: a!queryAggregation(
          aggregationColumns: {
            a!queryAggregationColumn(
              field: "startDate",
              groupingFunction: "YEAR",
              alias: "startDate_year",
              isGrouping: true
            ),
            a!queryAggregationColumn(
              field: "startDate",
              groupingFunction: "MONTH",
              alias: "startDate_month",
              isGrouping: true
            ),
            a!queryAggregationColumn(
              field: "id",
              alias: "id_count",
              aggregationFunction: "COUNT"
            ),    
          }
        ),
        pagingInfo: local!pagingInfo
      ),
      fetchTotalCount: true
    ),
    {
      a!columnsLayout(
        columns: {
          a!columnLayout(
            contents: {
              a!gridField(
                totalCount: local!datasubset.totalCount,
                columns: {
                  a!gridTextColumn(
                    label: "Year",
                    field: "startDate_year",
                    data: index(local!datasubset.data, "startDate_year", null),
                    alignment: "RIGHT"
                  ),
                  a!gridTextColumn(
                    label: "Month",
                    field: "startDate_month",
                    data: a!forEach(
                      items: index(local!datasubset.data, "startDate_month", null),
                      /*Extract the month name so it is displayed on the grid */
                      expression: text(
                          date(
                            index(local!datasubset.data.startDate_year, fv!index, null),
                            fv!item,
                            1
                          ),
                          "mmmm"
                        )
                    )
                  ),
                  a!gridTextColumn(
                    label: "Employee Count",
                    field: "id_count",
                    data: index(local!datasubset.data, "id_count", null),
                    alignment: "RIGHT"
                  )
                },
                value: local!pagingInfo,
                saveInto: local!pagingInfo,
                spacing: "DENSE",
                borderstyle: "LIGHT"
              )
            }
          ),
          a!columnLayout(
            contents: {}
          ),
          a!columnLayout(
            contents: {}
          ),
          a!columnLayout(
            contents: {}
          )
        }
      )
    }
  )
)

Chart Expression

Copy and paste the following chart expression into your interface to see a chart that shows the count of employees by year/month. This expression also provides sample code that allows designers to:

  1. Show the month name and year in your locale.
  2. Display months on the chart for which there is no data returned in the query but are displayed as labels to show all months in a date range.

aggregate_data_by_date_chart.gif

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
=load(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: - 1,
    sort: {
      a!sortInfo(field: "year", ascending: true),
      a!sortInfo(field: "month", ascending: true),      
    }
  ),
  with(
    local!data: a!queryEntity(
      entity: cons!EMPLOYEE_ENTITY,
      query: a!query(
        logicalExpression: a!queryLogicalExpression(
          operator: "AND",
          filters: {
            /* Avoid null date values so time aggregations */
            /* work properly                              */
            a!queryFilter(
              field: "startDate",
              operator: "not null"
            ),
            /* This example uses a date range to limit the */
            /* number of categories on the chart*/
            a!queryFilter(
              field: "startDate",
              operator: "between",
              value: {
                todate("1/1/2016"),
                todate("08/31/2017")
              }
            )
          }
        ),
        /* The 'groupingFunction' parameter allows us to */
        /* aggregate data on YEAR and MONTH              */
        aggregation: a!queryAggregation(
          aggregationColumns: {
            a!queryAggregationColumn(
              field: "startDate",
              groupingFunction: "YEAR",
              isGrouping: true,
              alias: "year"
            ),
            a!queryAggregationColumn(
              field: "startDate",
              groupingFunction: "MONTH",
              isGrouping: true,
              alias: "month"
            ),
            a!queryAggregationColumn(
              field: "id",
              alias: "value",
              aggregationFunction: "COUNT"
            ),
            
          }
        ),
        pagingInfo: local!pagingInfo
      ),
      /* Not required as this is the default behavior */
      /* but shown here for illustration purposes. */
      fetchTotalCount: false
    ).data,
    local!dates: a!flatten(
      a!forEach(
        /* Returns a list of years from the first year in the data set to now */
        items: enumerate(
          tointeger(local!data[length(local!data)].year) 
          - tointeger(local!data[1].year) + 1
        ) + tointeger(local!data[1].year),
        expression: with(
          local!year: fv!item,
          /* Start and end are the first and last months in the given year, */
          /*to make sure we don't go past today or before the first data point. */
          local!start: if(fv!isFirst,
            tointeger(local!data[1].month),
            1
          ),
          local!end: if(fv!isLast,
            tointeger(local!data[length(local!data)].month),
            12
          ),
          /* Uses start and end to enumerate the months in the given year. */
          a!forEach(
            items: enumerate(local!end - local!start + 1) + local!start,
            expression: {
              month: fv!item,
              year: local!year
            }
          )
        )
      )
    ),
    {
      a!columnChartField(
        label: "Count of Employees by Department",
        categories: a!forEach(
          items: local!dates,
          expression: text(
            date(
              index(local!dates, "year", null)[fv!index],
              index(local!dates, "month", null)[fv!index],
              1
            ),
            "mmmm yyyy"
          )
        ),
        series: {
          a!chartSeries(
            label: "Count of Employees",
            data: a!forEach(
              items: local!dates,
              expression: with(
                local!dataInThisYear: index(
                  local!data,
                  wherecontains(fv!item.year, local!data.year),
                  {}
                ),
                local!value: index(
                  local!dataInThisYear,
                  wherecontains(fv!item.month, local!dataInThisYear.month),
                  {}
                ),
                /* Set zeroes if there is no value for a data point */
                if(length(local!value) < 1,
                  0,
                  local!value[1].value
                )
              )
            )
          )
        }
      )
    }
  )
)

When aggregating data on Date or Date and Time fields make sure there are no empty or null values. Consider using a!queryFilter() with the operator parameter set to not nullto remove these values from your query.

FEEDBACK