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
=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(
        filter: a!queryFilter(
          field: "startDate",
          operator: "between",
          value: {
            todate(
              "1/1/2016"
            ),
            todate(
              "12/31/2018"
            )
          }
        ),
        aggregation: a!queryAggregation(
          aggregationColumns: {
            /*Year is not fetched but we want to sort by it */
            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!gridField(
        totalCount: local!datasubset.totalCount,
        columns: {
          a!gridTextColumn(
            label: "Year",
            field: "startDate_year",
            data: index(
              local!datasubset.data,
              "startDate_year",
              null
            )
          ),
          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: proper(
                text(
                  date(
                    index(
                      local!datasubset.data.startDate_year,
                      fv!index
                    ),
                    fv!item,
                    1
                  ),
                  "mmmm"
                )
              )
            )
          ),
          a!gridTextColumn(
            label: "Employee Count",
            field: "id_count",
            data: index(
              local!datasubset.data,
              "id_count",
              null
            )
          )
        },
        value: local!pagingInfo,
        saveInto: local!pagingInfo
      )
    }
  )
)

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, and also allows users to filter data by department. Notice the name of the month is concatenated to the year for better readability.

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
 =load(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 12,
    sort: {
      a!sortInfo(
        field: "startDate_year",
        ascending: true
      ),
      a!sortInfo(
        field: "startDate_month",
        ascending: true
      ),  
    }
  ),
  local!titleList: {
    "Consultant",
    "Manager",
    "HR",
    "Sales Associate",
    "Director",
    "Analyst"
  },
  local!selectedTitle,
  with(
    local!datasubset: a!queryEntity(
      entity: cons!EMPLOYEE_ENTITY,
      query: a!query(
        logicalExpression: a!queryLogicalExpression(
          operator: "AND",
          filters: {
            if(
              isnull(
                local!selectedTitle
              ),
              {},
              a!queryFilter(
                field: "title",
                operator: "=",
                value: local!selectedTitle
              )
            ),
            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",
              isGrouping: true,
              alias: "startDate_year"
            ),
            a!queryAggregationColumn(
              field: "startDate",
              groupingFunction: "MONTH",
              isGrouping: true,
              alias: "startDate_month"
            ),
            a!queryAggregationColumn(
              field: "id",
              alias: "id_count",
              aggregationFunction: "COUNT"
            ),      
          }
        ),
        pagingInfo: local!pagingInfo
      )
    ),
    {
      a!dropdownField(
        label: "Department",
        labelPosition: "ABOVE",
        placeholderLabel: "--- Select a Department ---",
        choiceLabels: local!titleList,
        choiceValues: local!titleList,
        saveInto: local!selectedTitle,
        value: local!selectedTitle,
        validations: {}
      ),
      a!columnChartField(
        label: "Count of Employees by Department",
        categories: a!forEach(
          items: index(
            local!datasubset.data,
            "startDate_month",
            null
          ),
          expression: proper(
            text(
              date(
                index(
                  local!datasubset.data,
                  "startDate_year",
                  null
                )[fv!index],
                fv!item,
                1
              ),
              "mmmm yyyy"
            )
          )
        ),
        series: {
          a!chartSeries(
            label: "Count of Employees",
            data: index(
              local!datasubset.data,
              "id_count",
              null
            )
          )
        }
      )
    }
  )
)
FEEDBACK