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

Interface patterns give you an opportunity to explore different interface designs. Be sure to check out How to Adapt a Pattern for Your Application.

Goal

Aggregate data from a data store entity, specifically the total number of employees by datet.

This recipe uses example data 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 expression into your interface to see a grid that groups data by year and month. Notice the name of the month is obtained using an expression and it is internationalized.

This kind of pattern is very easy to create using the query editor.

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
a!gridField(
  data: a!queryEntity(
    entity: cons!EMPLOYEE_ENTITY,
    query: a!query(
      aggregation: a!queryAggregation(
        aggregationColumns: {
          a!queryAggregationColumn(
            field: "startDate",
            alias: "startDate_year",
            isGrouping: true,
            groupingFunction: "YEAR"
          ),
          a!queryAggregationColumn(
            field: "startDate",
            alias: "startDate_month",
            isGrouping: true,
            groupingFunction: "MONTH"
          ),
          a!queryAggregationColumn(
            field: "id",
            alias: "id_count",
            aggregationFunction: "COUNT"
          )
        }
      ),
      logicalExpression: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          /* Limit results to 2016, 2017, and 2018. */
          a!queryFilter(
            field: "startDate",
            operator: "between",
            value: {
              date(2016,1,1),
              date(2018,12,31)
            }
          )
        },
        ignoreFiltersWithEmptyValues: true
      ),
      pagingInfo: fv!pagingInfo
    ),
    fetchTotalCount: true
  ),
  columns: {
    a!gridColumn(
      label: "Year",
      sortField: "startDate_year",
      value: fv!row.startDate_year
    ),
    a!gridColumn(
      label: "Month",
      sortField: "startDate_month",
      /* We pass the necessary date elements so we can use
         the text function to return the name of the month. */
      value: text(
        date(fv!row.startDate_year, fv!row.startDate_month, 1),
        "mmmm"
      )
    ),
    a!gridColumn(
      label: "Employee Count",
      sortField: "id_count",
      value: fv!row.id_count
    )
  },
  initialsorts: {
    a!sortInfo(
      field: "startDate_year",
      ascending: true
    ),
    a!sortInfo(
      field: "startDate_month",
      ascending: true
    )
  },
  secondarysorts: {
    a!sortInfo(
      field: "startDate_month",
      ascending: true
    )
  }
)
FEEDBACK