Basic functions of new column

1.Overview

1.1 Version

1.2 Application scenario

Calculate and obtain a new field without affecting the existing fields.

For example, if you already have the fields "Sales" and "Cost", you can obtain the field "Gross Profit" by subtracting these two fields in the new column.

1.3 Function introduction

A new column is a new data column obtained by the business person by calculating the existing data column without affecting the original data, which is saved in the business package for subsequent business analysis. For example, the conversion of data format, time difference, group assignment, etc., you can use the new column function.

Note: Starting from the version after 2018-11-16, the week calculation logic of the annual week granularity adopts the iso standard week logic, the first day of each week starts on Sunday, and each year contains 52 or 53 weeks, and the first week of each year must contain the 4th of January. This ensures that there are 7 days of data per week, which is more in line with the business scenario. The week start time can be modified in Section 2.9 of the General Parameters.

2. Formula/function

2.1 Example

1) Select "FACT_SALE_CONTRACT" under "Data Preparation" as the sample data and create a self-service data set. As shown in the figure below.

2) Select the fields by checking "FACT_SALE_CONTRACT" all the fields, and add new columns, as shown below.

4）Select "Formula/function". Use Contract Total Amount/Purchase Quantity to obtain a new "Average Price" field and select "Value" for the field type, as shown below.

Note: The default field type is automatic, and the system automatically determines the field type of the field according to the generated results.

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

5）Click OK to see the new data column "Average Price" in the data preview box, as shown in the following figure.

3. Time Difference

3.1 Example

After selecting the time difference method in the new column setting interface, you can select the time difference calculation formula and unit on the right side of the interface.

1）Select "Time Difference". Set the formula as "Current Time" minus "signing time", set the time difference unit as "Month", and enter the new column name as "Contracted Time to Date" to get the number of months between the two times, as shown in the following figure.

Note: The time difference unit contains seven types: year, quarter, month, day, hour, minute, and second. No matter what time difference unit is selected, the time difference field after the calculation is a value field and an integer, no decimal is retained.

2) You can see the new data column "Contracted Since" in the data preview box, as shown below.

4. Get time

Get the time can be directly obtained from the original date field of the different time unit.

For example, the original field is "2017-01-03 12:00:00": get its year: 2017; get its month: 01.

4.1 Example

1）Add a column and select "Get time". Select the field "Signing time" and set the Get time to "year", as shown in the following figure.

The units for getting time include 17 types: Year, quarter, month, Month day, week, day, weeks, Hour, Minute, Second, Year Quarter, Year Month, number of weeks in year, Year Month Day, Year Month Day Hour, Year Month Day Hour Minute, Year Month Day Hour Minute Second.

2) Click OK to see the new data column "Contract Year" in the data preview box, as shown in the following figure.

Note: The new time data column will be automatically judged according to the selected acquisition time unit type, without the need for field type selection. Such as the above acquisition of the contract signing year for the value type.

5. All values/within the group

The "data source field" can be grouped and then calculated statistically, or all values can be calculated directly without grouping. The supported range of each option is shown in the following table.

5.1 Example

For example, when you need to know what the average amount of each product is.

The following diagram shows that the value rule selects within the group all values, the value comes from contract total amount, the statistical method selects Average, and the grouping method selects "PRODUCTGUID".

2）In the data preview box, you can see the "average amount" of the same product name added, as shown below.

6. Cumulative value/within the group

The new column supports the use of cumulative value / within the group cumulative value to obtain.

If you select the cumulative value / within the group cumulative value, the cumulative statistics of cumulative value/ within the group cumulative value in the table will be calculated, and the current indicator will be summed up from top to bottom according to the sorting result of the previous data.

There are some differences between the direct link version and the draw version of this function, which will be described separately below.

6.1 Example of extracted version

1）The "contract total amount" is calculated cumulatively, as shown below.

2）You can see the new "cumulative contract amount" in the data preview box, as shown in the following figure.

6.2 Example of direct connection version

With the direct connection, you can sort the fields before the accumulation calculation. As shown in the figure below,  the direct connection has a sort function compared to the extraction data method. When "ORDERID" is checked and ascending order is selected, the system will accumulate the  amount according to the ID order.

Click "Preview" and the cumulative results in ID order are shown below.

7. Ranking/within the group

The new column is supported to be obtained using Ranking/within the group.

Users can rank fields other than text types to get a new ranked column. Ranking and within the group are supported.

7.1 Example

For example: ranking all contract amounts: the higher the contract amount, the smaller the value of the ranking.

1) Add a new column and select "Ranking/within the group", select the rule as "descending ranking", and the field is from "contract total amount". The following figure shows.

2）By clicking OK, you can see that the contract amount of each contract has been ranked: the larger the contract amount, the higher the ranking. As shown in the figure below.

8. Group assignment

The assignment basis for group assignment supports both "text" and "value" fields.

8.1 Text field

When the "text field" is selected as the assignment basis, the text field can be grouped.

8.1.1 Regrouping of text fields

To divide the original "DHL, Emery, UPS, US Mail" into two groups whose names are "DHL, Emery" and "UPS, US Mail", the new columns are automatically assigned according to the group names. The following figure shows.

After clicking "OK" to save, you can see the new grouped columns of the added contract types. As shown in the figure below.

8.1.2 Copy text field to other groups

For example, team  "LEO" needs to belong to both "SALE DEPARTMENT" and "OPERATION DEPARTMENT" groups, so you can use the "Copy to" function at this time.

Create a self-service dataset and select the fields under "Financial analysis of organizational performance of marketing line", as shown below.

Then add "New Column", select "Group Assignment", and group "team" to "SALE DEPARTMENT" and "OPERATION DEPARTMENT", as shown below.

Since "LEO" belongs to both "SALE DEPARTMENT" and "OPERATION DEPARTMENT" grouping, click "LEO" under "OPERATION DEPARTMENT", select "Copy to", a drop-down box will appear, select "SALE DEPARTMENT", click "OK", "LEO" will be displayed under both "SALE DEPARTMENT" and "OPERATION DEPARTMENT" grouping. Finally, click "OK", as shown below.

Note 1: "Copy to" is not supported for fields "not grouping".

Note 2: Copying fields to "not grouping" is not supported.

The effect is shown in the following figure.

Note: It supports moving fields that have been divided after using the "Copy to" function to other groups.

8.2 Value field

If the value field is selected as the assignment basis, the grouping assignment methods include Custom and Auto.

Custom allows you to set the group value range and group name by yourself, while Auto sets the group by default. The following figure shows.

8.2.1 Custom grouping

1）Custom grouping allows you to customize the number of groups, the range of each group, and the name of the group. Four groups are set for the contract amount, and the group names are "less than 100,000, 100,000 to 1 million, 1 million to 10 million, and more than 10 million". The following figure shows.

2）After clicking "OK" to save, you can see the new contract amount group assignment column. The following figure shows.

8.2.2 Auto grouping

Auto grouping allows you to set the interval, which will divide the data into groups based on the minimum and maximum values of the data according to the interval.

To set Auto grouping for the total amount, set the interval to 2000000, then the field will be divided into 5 groups. The following figure shows.

After clicking "OK" to save, you can see the new automatic contract amount group assignment column. The following figure shows.

8.3 Group assignment basis shows undefined

If you assign a group to a field with a divisor of 0, undefined will occur, so avoid the case where the divisor has 0. As shown in the following figure.