guid: b07aa464-a89d-45e7-b552-ad810b49605f liveboard: name: Insights | Group Aggregates visualizations: - id: Viz_1 answer: name: Total sales by region tables: - id: (Sample) Retail - Apparel name: (Sample) Retail - Apparel search_query: "[sales] [region] [date] = 'yesterday' sort by [sales] descending" answer_columns: - name: Total sales format: category: NUMBER numberFormatConfig: unit: NONE decimals: 0.0 negativeValueFormat: PREFIX_DASH toSeparateThousands: true isCategoryEditable: true - name: region table: table_columns: - column_id: Total sales headline_aggregation: SUM headline_client_state: "{\"headlineVizPropVersion\": \"V1\",\"columnProperty\": {\"summariesNumberFormatConfig\": {\"category\": \"NUMBER\",\"numberFormatConfig\": {\"unit\": \"THOUSANDS\",\"decimals\": 2.0,\"negativeValueFormat\": \"PREFIX_DASH\",\"toSeparateThousands\": true},\"isCategoryEditable\": true}}}" - column_id: region show_headline: false ordered_column_ids: - region - Total sales client_state: "" client_state_v2: "{\"tableVizPropVersion\": \"V1\",\"columnProperties\": [{\"columnId\": \"region\",\"columnProperty\": {}},{\"columnId\": \"Total sales\",\"columnProperty\": {}}]}" chart: type: COLUMN chart_columns: - column_id: Total sales - column_id: region axis_configs: - x: - region "y": - Total sales client_state: "" client_state_v2: "{\"version\": \"V4DOT2\",\"chartProperties\": {\"gridLines\": {},\"responsiveLayoutPreference\": \"USER_PREFERRED_ON\",\"chartSpecific\": {\"dataFieldArea\": \"column\"}},\"columnProperties\": [{\"columnId\": \"region\",\"columnProperty\": {}},{\"columnId\": \"Total sales\",\"columnProperty\": {}},{\"columnId\": \"Measure names\",\"columnProperty\": {}},{\"columnId\": \"Measure values\",\"columnProperty\": {}}],\"axisProperties\": [{\"id\": \"b92e1520-bdcc-47db-9dd7-4f908b1026d7\",\"properties\": {\"axisType\": \"Y\",\"linkedColumns\": [\"Total sales\"],\"isOpposite\": false}},{\"id\": \"7717a8c6-294f-443c-9f03-79dc5f77ede1\",\"properties\": {\"axisType\": \"X\",\"linkedColumns\": [\"region\"]}}]}" display_mode: TABLE_MODE viz_guid: ccab151d-4fae-4752-ac6e-b786a02e0c17 - id: Viz_2 answer: name: Sales Contribution tables: - id: (Sample) Retail - Apparel name: (Sample) Retail - Apparel formulas: - id: fx(sales percentage of global) name: fx(sales percentage of global) expr: "sum ( [sales] ) / [fx(global sales)] " was_auto_generated: false - id: fx(global sales) name: fx(global sales) expr: "group_aggregate ( sum ( [sales] ) , query_groups ( ) - { [region] } , query_filters ( ) )" was_auto_generated: false search_query: "[sales] [region] [date] = 'yesterday' sort by [sales] descending [fx(sales percentage of global)]" answer_columns: - name: Total sales format: category: NUMBER numberFormatConfig: unit: NONE decimals: 0.0 negativeValueFormat: PREFIX_DASH toSeparateThousands: true isCategoryEditable: true - name: fx(sales percentage of global) custom_name: Percentage of Global Sales format: category: PERCENTAGE percentageFormatConfig: decimals: 2.0 isCategoryEditable: true - name: region table: table_columns: - column_id: Total sales headline_aggregation: SUM headline_client_state: "{\"headlineVizPropVersion\": \"V1\",\"columnProperty\": {\"summariesNumberFormatConfig\": {\"category\": \"NUMBER\",\"numberFormatConfig\": {\"unit\": \"THOUSANDS\",\"decimals\": 2.0,\"negativeValueFormat\": \"PREFIX_DASH\",\"toSeparateThousands\": true},\"isCategoryEditable\": true}}}" - column_id: fx(sales percentage of global) show_headline: false - column_id: region show_headline: false ordered_column_ids: - region - Total sales - fx(sales percentage of global) client_state: "" client_state_v2: "{\"tableVizPropVersion\": \"V1\",\"orderedColumnIds\": [\"region\",\"fx(sales percentage of global)\",\"Total sales\"],\"columnProperties\": [{\"columnId\": \"region\",\"columnProperty\": {}},{\"columnId\": \"Total sales\",\"columnProperty\": {}}]}" chart: type: COLUMN chart_columns: - column_id: Total sales - column_id: fx(sales percentage of global) - column_id: region axis_configs: - x: - region "y": - Total sales - fx(sales percentage of global) client_state: "" client_state_v2: "{\"version\": \"V4DOT2\",\"chartProperties\": {\"gridLines\": {},\"responsiveLayoutPreference\": \"USER_PREFERRED_ON\",\"chartSpecific\": {\"dataFieldArea\": \"column\"}},\"columnProperties\": [{\"columnId\": \"region\",\"columnProperty\": {}},{\"columnId\": \"Total sales\",\"columnProperty\": {}},{\"columnId\": \"Measure names\",\"columnProperty\": {}},{\"columnId\": \"Measure values\",\"columnProperty\": {}},{\"columnId\": \"fx(sales percentage of global)\",\"columnProperty\": {}}],\"axisProperties\": [{\"id\": \"a384d3eb-5399-4102-af0e-3bf4e7668c3e\",\"properties\": {\"axisType\": \"Y\",\"linkedColumns\": [\"Total sales\"],\"isOpposite\": false}},{\"id\": \"93864eef-0a75-43fb-996a-da0cfa912364\",\"properties\": {\"axisType\": \"Y\",\"linkedColumns\": [\"fx(sales percentage of global)\"],\"isOpposite\": true}},{\"id\": \"2cf86cd6-c11f-41a3-a599-180a606f129b\",\"properties\": {\"axisType\": \"X\",\"linkedColumns\": [\"region\"]}}]}" display_mode: TABLE_MODE viz_guid: 7131c031-af43-4bb1-89f8-6c9c348321ac - id: Viz_3 viz_guid: 2a696cdc-78a4-4195-aa1e-54468df0fd1f note_tile: html_parsed_string: "

Objective:


What is my region's sales contribution towards the global revenue?

" - id: Viz_4 viz_guid: 72bdbb24-a410-40e1-930b-bb215694fda3 note_tile: html_parsed_string: "

Objective:


Which region is leading the pack in terms of sales? This ranking should be maintained even if we filter or sort the data in another order.


" - id: Viz_5 answer: name: Sales Leader tables: - id: (Sample) Retail - Apparel name: (Sample) Retail - Apparel formulas: - id: Sales Rank Order (Region) name: Sales Rank Order (Region) expr: "group_aggregate ( rank ( sum ( [sales] ) , 'desc' ) , query_groups ( ) , query_filters ( ) - { [region] } )" properties: column_type: ATTRIBUTE was_auto_generated: false search_query: "[sales] [region] [date] = 'yesterday' sort by [sales] descending [Sales Rank Order (Region)]" answer_columns: - name: Sales Rank Order (Region) - name: Total sales format: category: NUMBER numberFormatConfig: unit: NONE decimals: 0.0 negativeValueFormat: PREFIX_DASH toSeparateThousands: true isCategoryEditable: true - name: region table: table_columns: - column_id: Sales Rank Order (Region) show_headline: false - column_id: Total sales headline_aggregation: SUM headline_client_state: "{\"headlineVizPropVersion\": \"V1\",\"columnProperty\": {\"summariesNumberFormatConfig\": {\"category\": \"NUMBER\",\"numberFormatConfig\": {\"unit\": \"THOUSANDS\",\"decimals\": 2.0,\"negativeValueFormat\": \"PREFIX_DASH\",\"toSeparateThousands\": true},\"isCategoryEditable\": true}}}" - column_id: region show_headline: false ordered_column_ids: - region - Sales Rank Order (Region) - Total sales client_state: "" client_state_v2: "{\"tableVizPropVersion\": \"V1\",\"widthState\": [{\"columnId\": \"Sales Rank Order (Region)\",\"width\": 121.5999984741211}],\"columnProperties\": [{\"columnId\": \"region\",\"columnProperty\": {}},{\"columnId\": \"Total sales\",\"columnProperty\": {}}]}" chart: type: COLUMN chart_columns: - column_id: Sales Rank Order (Region) - column_id: Total sales - column_id: region axis_configs: - x: - Sales Rank Order (Region) "y": - Total sales color: - region client_state: "" client_state_v2: "{\"version\": \"V4DOT2\",\"chartProperties\": {\"gridLines\": {},\"responsiveLayoutPreference\": \"USER_PREFERRED_ON\",\"chartSpecific\": {\"dataFieldArea\": \"column\"}},\"columnProperties\": [{\"columnId\": \"region\",\"columnProperty\": {}},{\"columnId\": \"Total sales\",\"columnProperty\": {}},{\"columnId\": \"Measure names\",\"columnProperty\": {}},{\"columnId\": \"Measure values\",\"columnProperty\": {}},{\"columnId\": \"Sales Rank Order (Region)\",\"columnProperty\": {}}],\"axisProperties\": [{\"id\": \"4e2d6246-a80a-4191-8a3d-26a2ac1bb0ca\",\"properties\": {\"axisType\": \"Y\",\"linkedColumns\": [\"Total sales\"],\"isOpposite\": false}},{\"id\": \"af664e2e-bb1f-4278-8285-e3bb979f5fba\",\"properties\": {\"axisType\": \"X\",\"linkedColumns\": [\"Sales Rank Order (Region)\"]}}]}" display_mode: TABLE_MODE viz_guid: e5f7ca47-3521-431d-b9b8-82c5be9a98c0 - id: Viz_6 viz_guid: 6e82dda8-6c32-47b0-a2d3-e29be1b4fa8f note_tile: html_parsed_string: "

Objective:


How does yesterday's sales compare to the sales of the same day of the week this year?

" - id: Viz_7 answer: name: Sales Comp tables: - id: (Sample) Retail - Apparel name: (Sample) Retail - Apparel formulas: - id: Average Same Day Sales TY name: Average Same Day Sales TY expr: "average ( group_aggregate ( sum ( [sales] ) , query_groups ( ) + { [date] } , { year ( [date] ) = year ( [fx(yesterday)] ) and day_of_week ( [date] ) = day_of_week ( [fx(yesterday)] ) } ) )" was_auto_generated: false - id: fx(yesterday) name: fx(yesterday) expr: "add_days ( today ( ) , - 1 )" was_auto_generated: false search_query: "[sales] [region] [date] = 'yesterday' sort by [sales] descending [Average Same Day Sales TY]" answer_columns: - name: Average Same Day Sales TY format: category: NUMBER numberFormatConfig: unit: NONE decimals: 0.0 negativeValueFormat: PREFIX_DASH toSeparateThousands: true isCategoryEditable: true - name: Total sales format: category: NUMBER numberFormatConfig: unit: NONE decimals: 0.0 negativeValueFormat: PREFIX_DASH toSeparateThousands: true isCategoryEditable: true - name: region table: table_columns: - column_id: Average Same Day Sales TY headline_aggregation: TABLE_AGGR - column_id: Total sales headline_aggregation: SUM headline_client_state: "{\"headlineVizPropVersion\": \"V1\",\"columnProperty\": {\"summariesNumberFormatConfig\": {\"category\": \"NUMBER\",\"numberFormatConfig\": {\"unit\": \"THOUSANDS\",\"decimals\": 2.0,\"negativeValueFormat\": \"PREFIX_DASH\",\"toSeparateThousands\": true},\"isCategoryEditable\": true}}}" - column_id: region show_headline: false ordered_column_ids: - region - Total sales - Average Same Day Sales TY client_state: "" client_state_v2: "{\"tableVizPropVersion\": \"V1\",\"widthState\": [{\"columnId\": \"Average Same Day Sales TY\",\"width\": 144.6}],\"orderedColumnIds\": [\"region\",\"Average Same Day Sales TY\",\"Total sales\"],\"columnProperties\": [{\"columnId\": \"region\",\"columnProperty\": {}},{\"columnId\": \"Total sales\",\"columnProperty\": {}}]}" chart: type: COLUMN chart_columns: - column_id: Average Same Day Sales TY - column_id: Total sales - column_id: region axis_configs: - x: - region "y": - Total sales - Average Same Day Sales TY client_state: "" client_state_v2: "{\"version\": \"V4DOT2\",\"chartProperties\": {\"gridLines\": {},\"responsiveLayoutPreference\": \"USER_PREFERRED_ON\",\"chartSpecific\": {\"dataFieldArea\": \"column\"}},\"columnProperties\": [{\"columnId\": \"region\",\"columnProperty\": {}},{\"columnId\": \"Total sales\",\"columnProperty\": {}},{\"columnId\": \"Measure names\",\"columnProperty\": {}},{\"columnId\": \"Measure values\",\"columnProperty\": {}},{\"columnId\": \"Average Same Day Sales TY\",\"columnProperty\": {}}],\"axisProperties\": [{\"id\": \"e3f97ac0-2716-44c1-b6b0-aa42e61ac823\",\"properties\": {\"axisType\": \"Y\",\"linkedColumns\": [\"Total sales\"],\"isOpposite\": false}},{\"id\": \"0a96c78a-a846-4b23-b604-d4c208895b46\",\"properties\": {\"axisType\": \"Y\",\"linkedColumns\": [\"Average Same Day Sales TY\"],\"isOpposite\": true}},{\"id\": \"5f9a9e2f-1f53-4110-96e0-b4603d8b3314\",\"properties\": {\"axisType\": \"X\",\"linkedColumns\": [\"region\"]}}]}" display_mode: TABLE_MODE viz_guid: cfac09b2-170d-45a3-b1fc-e70d97310870 - id: Viz_8 viz_guid: 0d23b0ab-cd3b-4b74-9eb3-714c5955016e note_tile: html_parsed_string: "

Aggregation


sum ( sales )

" - id: Viz_9 viz_guid: defe8ec7-f0de-4932-8bd2-570a86522144 note_tile: html_parsed_string: "

Level of Detail / GROUP BY


query_groups ( )

+ { region }

- { date }

" - id: Viz_10 viz_guid: ffd71107-9287-4b44-a228-9b56eeb33597 note_tile: html_parsed_string: "

Filter Conditions


query_filters ( )

+ { region = 'west' }

- { date = '01/01/2025' }

" - id: Viz_11 viz_guid: b25df4d9-dc54-4c38-b459-671938610cd3 note_tile: html_parsed_string: "



What is the measure or attribute you want to calculate?

" - id: Viz_12 viz_guid: 86c1f049-1178-4710-b35d-0832da01d988 note_tile: html_parsed_string: "


Should we accept attributes from Search?

What is the grain of the aggregation?

" - id: Viz_13 viz_guid: 07896597-0fbe-41eb-a290-55136687436d note_tile: html_parsed_string: "


Should we accept filters from Search?

Are there any which must be enforced or ignored?


" - id: Viz_14 viz_guid: e9b1944c-4697-4050-a38f-f3950e6ba7eb note_tile: html_parsed_string: "

Group Aggregate


A flexible aggregation formula which allows you to explicitly define the grouping and filtering of a measure.



Documentation

https://docs.thoughtspot.com/cloud/latest/formulas-aggregation-flexible

" - id: Viz_15 viz_guid: 02ee1f18-761c-484a-9eeb-442e8de001b9 note_tile: html_parsed_string: "

Complex Runtime Logic !


Grouping aggregates perform sub-queries.


If filters are ignored globally, the formula can ask for a lot of data from the database at once.


If grouping is totally mismatched between multiple grouping functions, you may accidentally create a CARTESIAN PRODUCT.

" - id: Viz_16 viz_guid: e2e43e11-4daf-413a-b04f-3cb9469135e6 note_tile: html_parsed_string: "

There may be a better way.


Is there a ThoughtSpot feature that can be used instead of reaching for Group Aggregate first?


" - id: Viz_17 viz_guid: e1b85549-c865-4a2b-9c76-a2f91f8259f4 note_tile: html_parsed_string: "

What's in a name?


Since group aggregates hide logic from our users, it's important to remember to name them more explicitly to describe what is (or is not!) going on behind the scenes.



" filters: - column: - date is_mandatory: false date_filter: type: YESTERDAY oper: = display_name: "" layout: tabs: - name: Data description: "" tiles: - visualization_id: Viz_1 x: 5 "y": 0 height: 6 width: 4 - visualization_id: Viz_14 x: 2 "y": 0 height: 4 width: 3 id: b4d7a410-8d35-4d89-8c2f-511e539c6be1 - name: Formula description: "" tiles: - visualization_id: Viz_8 x: 2 "y": 0 height: 2 width: 4 - visualization_id: Viz_9 x: 2 "y": 2 height: 2 width: 4 - visualization_id: Viz_10 x: 2 "y": 4 height: 2 width: 4 - visualization_id: Viz_11 x: 6 "y": 0 height: 2 width: 4 - visualization_id: Viz_12 x: 6 "y": 2 height: 2 width: 4 - visualization_id: Viz_13 x: 6 "y": 4 height: 2 width: 4 id: cbbeebb8-b6fc-459b-8fae-5981ea8da034 - name: 1 - Percentage of description: "" tiles: - visualization_id: Viz_2 x: 5 "y": 0 height: 6 width: 4 - visualization_id: Viz_3 x: 2 "y": 0 height: 3 width: 3 id: fdd41585-baf4-45e4-891c-382ccc54c36e - name: 2 - Rank Order description: "" tiles: - visualization_id: Viz_4 x: 2 "y": 0 height: 3 width: 3 - visualization_id: Viz_5 x: 5 "y": 0 height: 6 width: 4 id: 08524fdc-f8fa-4287-984e-97ae3385e93e - name: 3 - Same Day description: "" tiles: - visualization_id: Viz_6 x: 2 "y": 0 height: 3 width: 3 - visualization_id: Viz_7 x: 5 "y": 0 height: 6 width: 4 id: b1620074-0afd-4183-bf07-84ae172ff6ba - name: Considerations description: "" tiles: - visualization_id: Viz_15 x: 2 "y": 0 height: 4 width: 3 - visualization_id: Viz_16 x: 5 "y": 0 height: 4 width: 3 - visualization_id: Viz_17 x: 8 "y": 0 height: 4 width: 3 id: c26fe820-8d6d-4792-8335-d9c3bd973d9f ordered_chips: - name: date type: FILTER