aggregateflex Step Type PropertiesUse the aggregateflex step type to perform aggregate queries on a Analytics dataset. An aggregate query summarizes rows, like returning one row per grouping. For example, this step type can return the total amount per sales rep.
| Field Name | Description |
|---|---|
datasets | An array of datasets used by this step. Specify the alias of each dataset. If the pigql attribute references a dataset that’s not specified here, the dashboard doesn’t render. |
broadcastFacet | Controls whether the step’s selections are broadcasted as facets. Faceting is when a selection in a widget filters other steps in the dashboard. Default is true. |
receiveFacetSource | Controls whether the step in the receiving query listens for broadcasted facets and applies them as filters. The mode can be “all”, “none”, “include”, or “exclude”. Use steps to list which steps to include or exclude. Default is all. |
isGlobal | This applies to global filters created before Winter ‘18 only. Newer global filters, those created in the global filter panel widget, don’t require a step. Default is false. You can apply this property only on steps that are connected to a global filter widget—all other steps ignore this property. A global filter filters other steps in the dashboard that have useGlobal set to true and reference the same dataset. |
label | Step label, which is primarily used for display in the dashboard designer user interface. |
query | The query used to retrieve results from a dataset. It must contain at least one grouping and can be in SAQL or compact form. Use a query in SAQL form to customize the query in a way that can’t be done using compact form. |
selectMode | Determines the selection interaction. The options for charts, tables, lists, and toggle selectors are: none, single, singlerequired, multi and multirequired. selectMode doesn’t apply to number, values table, date, range, and global filter widgets. |
start | The initial selections that are applied to the step when the dashboard first opens. |
type | Step type. Set to aggregateflex. If you bind a step property for an aggregateflex step, you must use the correct bindings syntax. For more information about bindings, see the Analytics Bindings Developer Guide. |
visualizationParameters | Visualization details about the step. For more information, see visualizationParameters Properties. |
useGlobal | Indicates whether to apply global filters to this step (true) or not (false). If the step is in SAQL form, you must also set autoFilter to true to apply the global filters. By default, the global filter widget filters compact-form steps only. |
The query can contain the following properties.
sources — The array of sources where data comes from. For more information, see sources Properties.sourceFilters — The filters applied to sources. When you use this object, the sources name is the steps key. the For more information, see filters Properties.aggregateFilters — The array of filters applied on aggregate columns, which are fields where an aggregate function, such as average or sum, is applied to a measure column. Aggregate columns are defined in columns under sources. For more information, see filters Propertiesorders — The array of sort orders applied to data in a pivot table. An order can contain the following properties:
name — The column or group name used to apply the sort order.ascending: The sort order applied to the data. Indicates whether to order the data in ascending order (true) or not (false).filters — The filters used to sort a measure column in a pivot table and is applied on a dimension value in the pivoted group. For more information about sorting data in a pivot table, see Organize and Summarize Data in a Pivot Table.nulls — The sort order applied to null values in the data. To sort null values last, set nulls to last. To sort null values first, set nulls to first.rowTotals — The array of dimension groups to which the values of each row are rolled up. To calculate rowTotals, you must also include columnGroups for the data.columnTotals — The array of dimension groups to which the values of each column are rolled up. If the query is a grain query, that is, all the columns are non-aggregate columns, or if the query is not grouped by any dimension columns, to calculate totals, set columnTotals to all.columnGroups — The pivoted dimension group, which is the last-defined group. If the defined group is a compound date, such as Year-Month for order date, then include each date grain in columnGroups. For example, ["OrderDate_Year", "OrderDate_Month"].limit — The maximum number of results that the step can return. When you create an aggregateflex step, by default, CRM Analytics sets limit to 2,000. To return more results, set the limit attribute accordingly. The higher you increase the limit, the longer the query takes. When a limit isn’t set, Analytics returns up to 10,000 results. For more information, see filters Properties.Compact Form 2.0 Query Example:
1"query": {
2 "sources": [
3 {
4 "columns": [
5 {
6 "field": ["sum", "Amount"],
7 "name": "A"
8 }
9 ],
10 "groups": [
11 "Product_Container",
12 "Order_priority"
13 ],
14 "filters": [
15 [
16 "Profit",
17 [16000],
18 "<",
19 ]
20 ],
21 "name": "SuperStoreSales"
22 },
23 {
24 "columns": [
25 {
26 "formula": "A*2",
27 "name": "B"
28 }
29 ],
30 "groups": [],
31 "filters": []
32 ]
33 },
34 ],
35 "sourceFilters": {
36 "SuperStoreSales" {
37 "filters": [
38 [
39 "Sales",
40 [
41 1,
42 100000
43 ],
44 ">=<="
45 ],
46 [
47 "Customer_Segment",
48 [
49 "Consumer",
50 "Corporate",
51 ],
52 "in"
53 ]
54 ]
55 }
56 },
57 "aggregateFilters": [
58 [
59 "A",
60 [10],
61 ">"
62 ]
63 ],
64 "orders": [
65 {
66 "name": "Product_Container",
67 "ascending": true,
68 "filters": [],
69 "nulls": "last"
70 },
71 {
72 "name": "Order_Priority",
73 "ascending": true,
74 "filters": [],
75 "nulls": "last"
76 }
77 ],
78 "rowTotals": ["Product_Container"],
79 "columnTotals": [
80 "Product_Container",
81 "Order_Priority"
82 ],
83 "columnGroups": ["Order_Priority"],
84 "limit": 2000
85}The query can contain the following properties.
filters — The filters to apply to the data. For more information, see filters Properties.
groups — The dimension to group by.
limit — The maximum number of results that the step can return. When you create an aggregateflex step, by default, CRM Analytics sets limit to 2,000. To return more results, set the limit attribute accordingly. The higher you increase the limit, the longer the query takes. When a limit isn’t set, Analytics returns up to 10,000 results. Limit only impacts the number of records returned for display. The limit doesn’t impact calculations across all records in the dataset. For instance, a query groups by Account Name and there are one million Account Names in a dataset. When the limit is 20, Analytics returns 20 records for display. But the summary row provides a total for the one million records.
measures — The measures returned by the query. If you provide an aggregate function for a measure, then the measure value must be a string, not an array.
order — Sort order (ascending or descending) of the first specified measure. To order the results in ascending order, set ascending to true. To order the results in descending order, set ascending to false. If you don’t want to impose a specific order, remove the entire "order" parameter.
Compact-form (1.0) Query Example:
1"query": {
2 "filters": [
3 [
4 "Account.Industry",
5 [
6 "Agriculture",
7 "Apparel",
8 "Banking",
9 "Biotechnology",
10 "Consulting",
11 "Education",
12 "Electronics",
13 "Energy",
14 "Engineering",
15 "Finance",
16 "Healthcare",
17 "Insurance",
18 "Manufacturing",
19 "Media",
20 "Retail",
21 "Technology",
22 "Telecommunications",
23 "Transportation",
24 "Utilities"
25 ],
26 "in"
27 ]
28 ],
29 "groups": [ "Account.Industry" ],
30 "measures": [
31 [
32 "avg",
33 "Amount"
34 ]
35 ],
36 "order": [
37 [
38 -1,
39 { "ascending": false }
40 ]
41 ]
42}The query can contain these properties:
pigql — Specify the SAQL query to retrieve data from a dataset. When you specify a SAQL query, you must specify the filters, limits, and ordering inside the pigql attribute. CRM Analytics ignores the following attributes if they are set under the query attribute: filters, limit, and order.
measures — Defines the fields included as measures. When using a SAQL-form query, you must include each measure in this parameter and in the pigql parameter. You can change the UI label of a measure by setting the display option.
1"count", "*", null, {
2 "display": "% of total flights"
3}groups — Defines the dimension fields to group by. When using a SAQL-form query, you must specify the group-by dimension in this parameter and in the group property in the pigql parameter.
SAQL-form Query Example:
1"query": {
2 "pigql": "q = load \"ServiceOpportunity3\";\n
3 q = filter q by 'Account.Industry' in
4 [\"Agriculture\", \"Apparel\", \"Banking\", \"Biotechnology\",
5 \"Consulting\", \"Education\", \"Electronics\", \"Energy\",
6 \"Engineering\", \"Finance\", \"Healthcare\", \"Insurance\",
7 \"Manufacturing\", \"Media\", \"Retail\", \"Technology\",
8 \"Telecommunications\", \"Transportation\", \"Utilities\"];\n
9 q = group q by 'Account.Industry';\n
10 q = foreach q generate 'Account.Industry' as 'Account.Industry',
11 count() as 'count';\n
12 q = order q by 'count' desc;\n
13 q = limit q 1000;",
14 "measures": [
15 [
16 "count",
17 "*",
18 "count"
19 ]
20 ],
21 "groups": [ "Account.Industry" ],
22 "measuresMap": {}
23}For more information about SAQL queries, see the Analytics SAQL Developer Guide.
1"steps": {
2 "Account_Owner_1": {
3 "type": "aggregateflex",
4 "query": {
5 "measures": [
6 [
7 "avg",
8 "Amount"
9 ]
10 ],
11 "groups": [
12 "Account_Owner"
13 ],
14 "order": [
15 [
16 "avg_Amount",
17 {
18 "ascending": false
19 }
20 ]
21 ]
22 },
23 "visualizationParameters": {
24 "options": {},
25 "type": "chart",
26 "parameters": {
27 "visualizationType": "hbar",
28 "autoFitMode": "keepLabels",
29 "theme": "wave",
30 "title": {
31 "label": "",
32 "fontSize": 14,
33 "subtitleLabel": "",
34 "subtitleFontSize": 11,
35 "align": "center"
36 },
37 "showValues": true,
38 "axisMode": "multi",
39 "binValues": false,
40 "bins": {
41 "breakpoints": {
42 "low": 0,
43 "high": 100
44 },
45 "bands": {
46 "low": {
47 "label": "",
48 "color": "#B22222"
49 },
50 "medium": {
51 "label": "",
52 "color": "#ffa500"
53 },
54 "high": {
55 "label": "",
56 "color": "#008000"
57 }
58 }
59 },
60 "dimensionAxis": {
61 "showAxis": true,
62 "showTitle": true,
63 "title": "",
64 "customSize": "auto",
65 "icons": {
66 "useIcons": false,
67 "iconProps": {
68 "column": "",
69 "fit": "cover",
70 "type": "round"
71 }
72 }
73 },
74 "measureAxis1": {
75 "sqrtScale": false,
76 "showAxis": true,
77 "customDomain": {
78 "showDomain": false
79 },
80 "showTitle": true,
81 "title": ""
82 },
83 "measureAxis2": {
84 "sqrtScale": false,
85 "showAxis": true,
86 "customDomain": {
87 "showDomain": false
88 },
89 "showTitle": true,
90 "title": ""
91 },
92 "legend": {
93 "show": true,
94 "showHeader": true,
95 "inside": false,
96 "descOrder": false,
97 "position": "right-top",
98 "customSize": "auto"
99 },
100 "tooltip": {
101 "customizeTooltip": false,
102 "showDimensions": true,
103 "dimensions": "",
104 "showMeasures": true,
105 "measures": "",
106 "showPercentage": true,
107 "showNullValues": true,
108 "showBinLabel": true
109 },
110 "trellis": {
111 "enable": false,
112 "showGridLines": true,
113 "flipLabels": false,
114 "type": "x",
115 "chartsPerLine": 4,
116 "size": [
117 100,
118 100
119 ]
120 },
121 "applyConditionalFormatting": true,
122 "showActionMenu": true,
123 "columnMap": {
124 "trellis": [],
125 "plots": [
126 "avg_Amount"
127 ],
128 "dimensionAxis": [
129 "Account_Owner"
130 ]
131 }
132 }
133 },
134 "datasets": [
135 {
136 "id": "0FbB00000000xHDKAY",
137 "name": "DTC_Opportunity_SAMPLE",
138 "label": "DTC Opportunity",
139 "url": "/services/data/v48.0/wave/datasets/0FbB00000000xHDKAY"
140 }
141 ],
142 "useGlobal": true,
143 "isGlobal": false,
144 "label": "Account_Owner_1",
145 "broadcastFacet": true,
146 "receiveFacetSource": {
147 "mode": "all",
148 "steps": []
149 },
150 "selectMode": "single"
151 }
152 }steps Properties for Compact Form and SAQL Form
The properties and JSON syntax in the query node of an aggregateflex step type vary based on whether the step is in compact form or SAQL form.
j