Create New Field

Overview

Feature Overview

When the fields provided by a dataset do not meet your analysis needs during chart creation, you can use Create New Field to build new fields on top of existing ones through calculations, grouping, or filtering, and then analyze data with those newly created fields.

Three methods are supported: creating grouped fields, filtered fields, and calculated fields. Users can choose the most appropriate method based on the actual business requirement.

Use Cases

In a sales analysis scenario, for example:

• If you need to analyze gross profit, but the raw data provides only sales amount, quantity, and cost price, you can create a calculated field for gross profit.
• If you want to analyze sales by region but the data includes only store information and not region information, you can create a grouped field to assign stores to regions and complete the regional sales analysis.
• If you want sales amount for specific product categories such as cosmetics, daily necessities, or food, you can create a filtered field with a condition like Product Category = Cosmetics, generate a new field such as Cosmetics Sales Amount, and analyze sales for that product category.

Create a Calculated Field

Overview

When charting, if the existing fields do not meet your needs, you can create a calculated field to obtain the required metric.

There are two ways to create a calculated field: in the dataset or in the card. If you want the calculated field to be reusable by all users, it is recommended to create it in the dataset. In practice, there is no difference in how the two are used.

Calculated fields support a wide variety of scenarios, including aggregation, arithmetic operations, window functions, string splitting and merging, and complex grouping. For common functions, see Common Spark Functions.

Function Details

The calculated field editor consists of five parts, as shown below:

1. Field Name: the name of the new field
2. Field Type: five types are available, namely text, numeric, date, date-time, and boolean
3. Function List on the left: provides a rich set of functions. In extracted mode, all Spark functions are also supported beyond the listed ones. For details, see Function List
4. Field and Parameter List in the middle: the field list shows all raw and newly created fields in the current dataset, while the parameter list shows global parameters preset by administrators
5. Formula Editor on the right: after a formula is entered, hints appear below it, and one-click formatting is supported to improve readability

Notes

1. In addition to typing field names manually, you can click a field or parameter in the middle area to insert it directly. If you type manually, the field name must be wrapped in square brackets [] to be recognized correctly.
2. When deleting a field or a function, it is treated as a whole and can be deleted in one operation, reducing manual effort.
3. When typing functions manually, matching functions are recommended automatically based on keywords.

Common Use Cases

For more examples, see the following references:

• Field Split Case Study
• Use Window Functions for Cumulative Calculation
• Use Distinct Count Functions for Windowing
• Combined Function Use Cases
• How to Use count_if
• Functions for Extracting Year and Month
• Sorting Functions and Use Cases

Create a Grouped Field

Overview

If you want to group dimension members or measure values, you can use Create Grouped Field.

For example, you can group the values of the dimension field Province into regions such as East China and South China and then analyze with Region as the dimension. You can also bucket the measure Sales Amount into ranges such as 0-100, 100-300, and 300-500 to analyze performance across value ranges.

Feature Overview

When you need to group dimension or measure fields, that is, merge values of the same type into groups based on specified conditions, you can use the grouped field feature.

There are two entry points, as shown below: the + button at the top right of the field list, or the settings button that appears when hovering over a field name.

Different grouping methods are provided for different field types:

Text fields: item grouping and condition grouping

Date/time fields: after grouping, the result becomes a text field. Three grouping methods are supported: item grouping, fixed step, and custom step

Numeric fields: after grouping, the result becomes a dimension field. Three grouping methods are supported: item grouping, fixed step, and custom step

Usage Instructions

Text Type

For text-type dimension fields, Item and Condition grouping are supported. Ungrouped items can either be merged or kept as separate items.

1. Item: select and group dimension members manually, as shown below:

   
   

   After creation, the new grouped field such as Region Grouping appears in the dimension field area. Hover over the field and use the settings icon to edit it again, regroup it, or delete it.

Ungrouped fields support two modes:

• Separate Items: every uncategorized dimension member is shown as its own group
• Merged Items: all uncategorized dimension members are merged into Others

1. Condition: create a group for data that matches a specified condition. Supported operators include equals, does not equal, contains, does not contain, starts with, ends with, does not start with, and does not end with.

   
   

Date Type

After grouping, date/time fields become text fields. Three grouping methods are supported: item grouping, fixed step, and custom step. Ungrouped fields can either be merged or kept separate.

1. Item: manually group dimension members one by one. The logic is the same as item grouping for dimension fields.

2. Fixed Step: when you want to group dates according to a regular interval, such as several days, weeks, months, quarters, or years, use this option.

   Notes

   1. Aggregation granularity can be configured as day, week, month, quarter, or year.
   2. A start time is required.
   3. Group step must be a positive integer. For example, when the step is 2 and the aggregation granularity is month, each group contains two months, and the interval is closed on both sides.

   For example, if you want to analyze sales in 15-day intervals since January 1, 2022, set the aggregation granularity to day, the start time to January 1, 2022, and the step to 15.

   

3. Custom Step: when you want to define several time periods manually as groups, use custom step. Only day granularity is supported.

   

Numeric Type

After grouping, numeric fields become dimension fields. Three grouping methods are supported: item grouping, fixed step, and custom step. Ungrouped fields can either be merged or kept separate.

1. Item: manually group dimension members one by one. The logic is the same as item grouping for dimension fields.
2. Fixed Step: when you want to divide a specified numeric range into regular intervals, use this option. In the example below, sales amount from 0-5000 is grouped with a fixed step of 1000. Each interval is left-closed and right-open by default.
3. Custom Step: when you want to define the range of each group yourself, use custom step. In addition to customizing the interval range, you can also customize whether each side of the interval is open or closed.

Creating a LOD Field

Overview

LOD (Level of Detail) fields are used to specify the calculation granularity of metrics. When the chart's current dimensions don't match the dimensions required for metric calculation, LOD fields can fix, add, or exclude certain dimensions to allow metrics to aggregate at the specified granularity.

For example, when a chart displays sales amount by Province and City, if you need to display the Province Sales Amount next to each city, you can create an LOD field calculated with Province as the fixed dimension. This way, even when the chart dimensions are refined to the city level, the LOD field still aggregates sales amount by province.

Feature Overview

In the dataset field area of the card editing page, click the + on the right side of the field area and select Create LOD Field to enter the LOD field editor.

After entering the LOD field editor, you need to configure the field name, LOD type, specified dimensions, and calculation content. The Preview Expression on the right side displays the LOD expression in real time based on the configuration.

Configuration Item	Description
Field Name	Enter the name of the new field.
Field Type	LOD field type is numeric.
LOD Type	Select the calculation method for the LOD field: FIXED, INCLUDE, and EXCLUDE. FIXED: Calculates based on specified dimensions, not fully following the chart's current dimensions. INCLUDE: Adds specified dimensions to the chart's current dimensions for calculation. EXCLUDE: Excludes specified dimensions from the chart's current dimensions for calculation.
Specified Dimensions	Select the fields used for LOD calculation. FIXED can omit dimensions, indicating calculation by global scope; INCLUDE and EXCLUDE must specify dimensions. Supports selecting dimension fields or numeric fields. Does not support selecting LOD fields, calculated fields using LOD fields, or aggregated measure fields.
Calculation Content	Select the aggregation method and the fields participating in the calculation. Common aggregation methods include Sum, Average, Count, Distinct Count, Minimum, and Maximum. Sum/Average: Only supports numeric type fields; does not include LOD fields, calculated fields using LOD fields, or aggregated measure fields. Count/Distinct Count/Minimum/Maximum: Supports dimension and numeric type fields; does not include LOD fields, calculated fields using LOD fields, or aggregated measure fields.
Preview Expression	Generates an expression preview based on LOD type, specified dimensions, and calculation content. For example: {FIXED [Province] : SUM([Sales Amount])}

Usage Instructions

Taking the following chart scenario as an example: the user wants to display the sales amount summary of the province to which each city belongs in the city detail view. In this case, you can create an LOD field Province Sales Amount.

1. Click + > Create LOD Field on the right side of the dataset field area to enter the LOD Field Editor dialog.

   

2. Edit the LOD field.

   1. Set the field name to Province Sales Amount, and select FIXED as the LOD type.
   2. Select Province in the Specified Dimensions.
   3. Select Standard Aggregation > Sum > Sales Amount in the Calculation Content.
   4. Confirm the preview expression on the right is {FIXED [Province] : SUM([Sales Amount])}, then click OK.
   

3. After successful creation, Province Sales Amount appears in the field list. Drag Province and City into the Dimension area of the plotting area, and drag Sales Amount and Province Sales Amount into the Measure area. The chart displays sales amount by city, and simultaneously displays the sales amount summary of the province to which the city belongs in the Province Sales Amount column.

   

Supported Scope

Supported

1. When the dimensions specified in FIXED are not used as chart dimensions, the system can still calculate the LOD field and display the calculation results in association with the chart data.
2. LOD fields can be dragged into the measure plotting area as numeric fields, and advanced calculations such as ranking and percentage can be configured.
3. Multiple LOD fields can be used in the same chart.
4. LOD fields can be used to create new calculated fields. For example, SUM([Sales Amount]) / SUM([Province Sales Amount]) can be used to calculate the regional proportion. Fields that can be used with LOD fields in calculated fields are subject to the fields already used in the current chart's standard aggregation or LOD aggregation.
5. Supports nested use of LOD fields, and also supports subtotal and total calculations on LOD fields.

Not Supported

1. Specified dimensions and calculation content do not support selecting LOD fields, calculated fields using LOD fields, or aggregated measure fields.
2. LOD fields and calculated fields using LOD fields do not support being dragged into the dimension or comparison plotting area.
3. Does not support using LOD fields or calculated fields using LOD fields for filtering.
4. Does not support using LOD fields or calculated fields using LOD fields for sorting.
5. Does not support setting regular field filters that do not affect LOD calculations.

Create a Filtered Field

Overview

When you need to view data under specific conditions, you can create a filtered field and apply filter conditions to complete metric analysis under those conditions.

Feature Overview

A filtered field applies specified conditions to an existing numeric field and forms a new field from the filtered result. It supports AND and OR combined conditions, with up to five levels of condition nesting.

Usage restriction: only numeric fields support creation of filtered fields. Dimension fields, calculated fields, and aggregated measures are not supported.