If the data that you want to analyze needs to be processed based on the source data, instead of being obtained from a data table, you can create a calculated field.
COUNT(DISTINCT [customer name])to calculate the number of customers based on customer names.
- Basic operations
Example: Use the formula
[Transaction amount]/[Number of customers]to calculate the unit price.
- String segmentation and merging
CONCAT([province], [city])to merge the province and city fields.
- Complex grouping
CASE WHEN [Transaction amount]>1000 AND [Number of transactions]>5 THEN 'VIP' ELSE 'normal' ENDto specify the customers who match the specified condition as VIP customers.
- On the Preview tab of the dataset creation page, click Create Calculated Field.
- In the New Calculated Fields dialog box, configure the parameters and click OK.
- Enter a field name in Label Name.
- Select fields in the Click to reference field and Click to reference function sections and edit the field expression. When you edit an expression, take note of the following points:
- After you enter a left bracket ([) in the Field Expression field, a measure list appears for you to select the desired measure.
- You can use only the functions that are supported by each data source.
You can view the functions that are supported by a specific data source in the function section on the right side of the New Calculated Fields dialog box or query the functions that are supported by the data source type.
- Specify Data Type and Type. Then, click OK.
- Count with deduplication:
Quick BI automatically aggregates the data of the dimensions that are configured on a dashboard for the calculated fields that are configured for aggregation.
- Addition, subtraction, multiplication, and division are supported.
- For example, you can use
SUM([transaction amount])/COUNT(DISTINCT [customer name])to calculate the unit price.Note If the denominator is 0, process data in calculated fields to avoid calculation errors.
CASE WHEN [Customer name] is null THEN null ELSE SUM([Transaction amount] )/COUNT(DISTINCT [Customer name]) END
Complex groupingIf you want to group dimensions, you can create group dimensions to group the dimensions based on a single field. If you want to group data based on the combination of multiple fields, you can use the
CASE WHENfunction. Example:
CASE WHEN [Transaction amount]>5000 AND [Number of transactions]>40 THEN 'VVIP' WHEN [Transaction amount]>1000 AND [Number of transactions]>5 THEN 'VIP' ELSE 'normal' END
If a table contains the province and city fields, you can use the CONCAT function to merge provinces and cities into one field.
CONCAT(a, b, c, d): Add the fields that you want to merge to parentheses and separate the fields with commas (,). If you want to add a delimiter between multiple columns when you merge strings, you can enclose the delimiter with a pair of single quotation marks (').
You can use string functions to process text data.The string processing method varies based on database functions. In this example, MySQL functions are used.
- Truncate a string.
SUBSTRING([customer name],1,1): specifies to extract the first character of the [customer name] field.
SUBSTRING([field]: specifies the substring that you want to truncate from the start of a string.
- Find the position of a substring in a string.
INSTR([Customer name], 'East'): checks whether [Customer name] contains the word
'East'. If the word exists, the position of the word in the customer name is returned. If the word does not exist, 0 is returned.
- Truncate a string.
Date and time processing
Quick BI automatically splits the values in the date and time fields into multiple time granularities. You can specify display formats for fields of different time granularities to meet the business requirements for different time formats of date and time granularities in different scenarios.
You can also use database date-related functions to process data. You can enter DATE in the search box to search for related functions in the function list.The date and time processing method varies based on database functions. In this example, MySQL functions are used.
CURRENT_DATE: returns the current date.
DATEDIFF(CURRENT_DATE, [Recruitment date]): returns the number of days from the day when an employee is hired in an enterprise to the current day.
ADDDATE([Payment date], 365): returns the date that is 365 days after the payment date.