Practical Tips for Cards

How to Quickly Replace 0 in a Table with Text in Batches

Set conditional formatting for numeric fields in the table and replace them with text. Set Save and Sync as needed, and select other cards if applicable.

How to Highlight Comparisons Between Two Columns or Against the Average

Use the Set Conditional Formatting feature:

1. Compare values between two columns: Set Conditional Formatting -- New Column Rule -- select Comparison as the type. Example: when target sales are less than sales, use red text on a yellow background.

2. Compare against the average: Set Conditional Formatting -- New Column Rule -- select Comparison as the type.

Note

In the rule editor, the field used to enter conditional formatting is selected on the left by default. You can change it.

How to Set Different Colors for Different Columns or Bars

• Table card: configure this through Set Conditional Formatting -- Column Rules.
• Bar chart/column chart: drag the dimension field into the color field well based on the dimension field.

How to Hide Fields in a Card

1. Use column permissions on the dataset to control whether the column is hidden. Alternatively, remove the column through ETL and then apply the ETL output dataset to the card.

2. If the field must be used in the card dimension field well but should not be directly visible to users, such as an ID card number, set Conditional Formatting -- Column Rules to replace the field content with a space, set the field alias to a space, adjust the column width, and set no border. This makes the field visually invisible.

How to Remove the First N Zeros from Text Data

Solution:

1. Method 1: Use a regular expression to remove zeros.

REGEXP_REPLACE([商品编号] ,"^(0+)", "")

2. Method 2: Use *1 or /1 to remove zeros and convert the result to text with a string formula.

string( int ([商品编号]/1 ))
或者
cast( int ([商品编号]/1 )as string)

Notes:

1. int is used here because after text is multiplied or divided by 0 and then converted directly to text with string, one decimal place is included. The int function solves this issue.

2. If you directly use *0 or /0 and set the field format to text, the actual format may not be converted successfully, which can cause issues for later data processing and card creation. Currently, formula conversion is still needed.

How to Get the Maximum or Minimum Value Among Multiple Fields

Solution: Use the least() and greatest() functions in a new field. The syntax is least(A,B,C,...) and greatest(A,B,C,...). You can include multiple fields for comparison, and both numeric and date fields are supported.

How to Set the Format for Exported Card Data

Select Table Template Settings, upload the configured table template, and then select Export Table Data.

How to Clearly Distinguish Positive and Negative Colors in a Column Chart

1. This also applies to bar charts. Drag the Value field into Color, then select Color Settings.

2. Set custom thresholds for the colors.

Use Global Parameters to Highlight Filtered Data

1. Prepare a single-value or multi-value text global parameter as needed.

2. Create a card and create a field that references the parameter for judgment. Set the field type to Boolean.

Single selection: [省份] = [DYNAMIC_PARAMS.多维度筛选]

Multiple selection: [省份] in ([DYNAMIC_PARAMS.多维度筛选])

3. For chart types with a color field well, such as column charts, bar charts, and maps, place the newly created field Filter into the color field well and set the field color.

4. For table types, set Conditional Formatting -- Row Rules, and set the background color when Filter equals true.

5. Add a Province filter on the page and link it to the global parameter in the card. The final result is shown below:

How to Set a Transparent Card Background Color

In Card Settings -- Card Background on the right, set the corresponding "A" value to 0:

Extension: color codes. Original source: What Is the Code for Transparent Color in CSS Color Codes?

Common color codes are divided into four types:

1. Common color words, such as green, yellow, red, and transparent.

2. Six-character color codes starting with #, such as #FF0000 (red), #000000 (black), and #F9F900 (yellow).

   

3. RGB color values, written as rgb(parameter 1, parameter 2, parameter 3). The three parameters represent r, g, and b.

4. R: red value. Positive integer or percentage.

5. G: green value. Positive integer or percentage.

6. B: blue value. Positive integer or percentage.

7. rgba(parameter 1, parameter 2, parameter 3, parameter 4). The first three parameters are the same as RGB above. The fourth parameter represents opacity, with a value between 0 and 1. 0 means opacity is 0, namely transparent, and 1 means opacity is 1, namely 100%.

How to Remove Table Subtotals

For a regular table, click the dimension field and choose not to display subtotals. Subtotals cannot be removed from grouped tables.

How to Calculate the Total of TOP 10 When Only 10 Dimensions Are Displayed

Use Advanced Calculation -- Ranking -- TOP N instead of limiting the number of displayed dimensions. Then manually change the subtotal/total calculation method to Calculate Based on Aggregated Data.

How to View Only the Top 20 Records

Method 1: Sort the value field in descending order, then set the display count to 20 in the dimension.

Method 2: Use Advanced Calculation -- Ranking on the value field and set TOP N to the required number.

How to Show Only Top N in a Pie Chart and Group the Rest as "Other"

In the pie chart, sort the value field in descending order. Open the editing page right panel -- Pie Chart Settings -- Display Dimension Count, then manually enter the number of dimensions to keep and the color for "Other".

How to Sort by Time Ascending but Show Only the Last N Days

When limiting the display count in Dimension does not meet the requirement, directly select the last N days in Filter, then use Sort.

How to Set Ranking Icons

1. On the table editing page, select Show Row Number.

2. Select the icon to display. Currently, only the icons in the options are supported.

How to Link a Filter Date to Data from the Previous N Days

1. Create a date-type global parameter and a parameter filter.

2. In the card, create a Boolean field and use the DATEDIFF function to determine whether the data is from x days before the selected date. Put this field into the filter conditions. If sorting is needed, put the date field into the sorting dimension.

How to Sort a Table by Comparison Field First

Directly drag the comparison measure name and comparison field. As shown below, drag the Date field before the measure name to sort by Date first, then display the corresponding fields under each date.

City-Level Map Is Not Displayed

In Map Display Settings -- Map Display Range, select the outline as needed:

• National outline -- Dimension: province
• Provincial outline -- Dimension: city
• City outline -- Dimension: district/county

Heat Map or Composite Map Does Not Display the Map

Check the placement order of longitude and latitude. Latitude should be placed first, followed by longitude.

How to Set Map Colors

1. Open the card editing page, go to the left editing panel, select the specific field in the Color field well, and select Color Settings.

2. Set the corresponding colors as needed. A gradient can contain at most 10 levels. If colors remain the same after quick threshold settings, the data may contain many null values.

3. In Map Display Settings on the right, set the map base color, border color, hover background color, and other styles.

How to Adjust Table Column Width and Wrap Long Text

• Column width: In table mode, you can manually drag to adjust column width. To control table column width, enter the width on the right.

• Header wrapping: Because of underlying framework limitations, line breaks in header fields by pressing Enter are not currently supported. If headers need to wrap, the new BI version can automatically wrap by dragging column width. You can also set an alias on the dimension field and add a line break at the desired wrap position
  
  to specify where the line should wrap.

Table Pagination Shows Different Numbers of Columns on Page 1 and Page 2

Column width is automatic by default and changes based on data length. If you want the same columns to appear on both pages, set fixed column widths. Text wraps automatically by default.

How to Fix the Table Header

Table headers are fixed by default. When comparison measures or grouping exist, they are also treated as table headers and fixed by default.

How to Display a Detail List Through Linkage

1. Create a table card. Create a new field of text type and use the collect_set function to integrate details into an array. Drag it into Value and set the aggregation method to No Processing.

2. If you need a distinct count, replace the formula with: size(collect_set([字段])over(partition by [分组列名])), and set the type to numeric.

3. If you do not need a distinct count, replace the formula with: size(collect_list([字段])over(partition by [分组列名])), and set the type to numeric.

2. On the original card, use the Linkage feature and link it to the detail list card you just created.

How to Get Data from the Last Day of the Selected Date Range

Method 1

Set the start and end time of the date selector as two global parameters. This requires creating two date filters. Then calculate the member data field based on the global parameter for end time.

1. Create two date-type global parameters: Start Time and End Time.
2. Create two date filters.

1. Reference the End Time parameter in the card.

1. Set linkage between the card and filters.

Method 2

The principle is the same as Method 1, but only one date filter is used.

1. Create two date-type global parameters: Start Time and End Time.
2. Create one date filter.
3. Reference the two time parameters in the card. Start Time can be placed directly in the card field as linkage for the time filter. End Time can be used for judgment, with the format set to Boolean.

1. Set linkage between the filter and card.

How to Calculate Completion Rate When the Target Is Negative

1. Use a bullet chart instead of a gauge chart. In the example below, Guangdong's target is -60 and the achieved amount is -50, which is more than 10 away from the target. This can be seen clearly in the chart.

Bullet charts include column bullet charts and bar bullet charts. Use the appropriate type based on the actual scenario:

2. A bullet chart can visually show the distance between the target and achieved value, but it cannot calculate a percentage. If you need to show a percentage, create a field in table form using the following formula: Completion Rate = [2 - (Actual Completed Amount / Target Value)] x 100%.

How to Set Date Periods Such as Biweekly, Bimonthly, and Half-Year

1. The date field drop-down list has preset periods: year, quarter, month, week, weekday, and day for date fields; hour, minute, and second are also available for timestamp fields.

2. If other periods are frequently needed, use Add Group.

How to Show the Filtered Time Range on a Card

1. Create two date-type global parameters as start time and end time. Set default values based on the actual scenario.

2. Use the two global parameters you created to create two parameter filter cards.

3. Create three calculated fields on the linked card:

4. Date-type field: "Date Field" case when [日期]>=[DYNAMIC_PARAMS.开始时间] and [日期]<=[DYNAMIC_PARAMS.结束时间 1] then [日期] else null end

5. Text-type field: "Time Range" concat(string([DYNAMIC_PARAMS.开始时间]),"-",string([DYNAMIC_PARAMS.结束时间 1]))

6. Numeric field: "Total Sales Amount" case when [日期]>=[DYNAMIC_PARAMS.开始时间] and [日期]<=[DYNAMIC_PARAMS.结束时间 1] then [销售金额] else null end

7. Result:

Why Can't I See the Card Query SQL?

Make sure the switch Allow Viewing Query SQL or Spark Script is enabled in Admin Settings -- System Management -- Advanced Settings.

Meaning of "Selected Date" in Advanced Calculation Period Comparison

Selected Date = Baseline Date.

Selected Date is today ==> compare today with yesterday.

Selected Date is yesterday ==> compare yesterday with the day before yesterday.

Selected Date is the previous n days ==> compare the previous n days with the previous n-1 days.

Selected Date is this year ==> compare this year with last year.

Selected Date is last year ==> compare last year with the year before last.

Selected Date is the previous n years ==> compare the previous n years with the previous n-1 years.

Slash Usage in New Fields: / and \

1. Backslash "\": escape character. In Guandata, when "\" is used, it disappears. Result:

2. Forward slash "/": retained. Result:

3. Double backslash "\\": one backslash is retained. Result:

Advanced Calculation for Aggregated Fields: Percentage Logic

1. Advanced Calculation after aggregation -- Percentage (Sum) logic: sum([销售金额])over(partition by [省份]).

• For example, if the calculation result for Zhejiang is 60, every row corresponding to Zhejiang has 60 appended, meaning 5 x 60 = 300. Jiangsu is 66, meaning 4 x 66 = 264. Therefore, Zhejiang's percentage is 300/564 = 53.19%, and Jiangsu's percentage is 264/564 = 46.81%.

2. Advanced Calculation after aggregation -- Percentage (No Processing) logic: when No Processing is selected, the system takes the maximum value by default.

• For example, after sum aggregation, Zhejiang's No Processing value is 60 and Jiangsu's is 66. If percentage also uses No Processing, the default numerator is each respective value and the denominator is the maximum value of the two, 66. Therefore, Zhejiang's percentage is 60/66 = 90.91%, and Jiangsu's percentage is 66/66 = 100%.

3. To calculate Zhejiang's percentage as 60/(60+66) and Jiangsu's as 66/(60+66), directly drag the "sum aggregation" field, or the source data field, from the previous example into Value, and use Advanced Calculation -- Percentage -- By Column.

How to Use Reference Lines to Create a Four-Quadrant Chart

1. Create a bubble chart or scatter chart. For details, see Bubble Chart and Scatter Chart.

2. Add a reference line for the primary value axis, or x-axis:

   

3. Add a reference line for the secondary value axis, or y-axis:

How to Redirect Different Rows in the Same Table to Different Pages

Requirement: 1. Use a specified field hyperlink to redirect to another page. Guandata's built-in redirect opens the target page when any table field is clicked, but users do not want this interaction. 2. Pass the current dimension information to the target page during redirection. 3. Redirect to different pages based on different dimension information.

Result:

Implementation:

1. Create dashboards for two target pages. The target pages need filters that can receive parameters. Copy the target page links and filter IDs.

2. In the table card where the redirect is configured, create a text field that concatenates the target page URL with filter conditions. Then drag it into the dimension field well and set Display as Hyperlink.

case 
when [省份]='山西省' then concat('https://app.guandata.com/page/b6a88413f23fc4b188cc1416?b54c1d25fb86d40748b7d89c=',[城市],'&w2775bf304ebd412c8b9dd35=',[日期2]) 
when [省份]='辽宁省' then concat('https://app.guandata.com/page/uf668e9ecc3fa4af6bdbaf81?ne6ce9f8526494ac4afd0983=',[城市],'&g1f6f5afd283b434585e25bd=',[日期2]) end

How to Sum Distinct Values

Requirement: Under the same dimension values, such as store and product, the numeric value, such as order count, is the same. You want each value under the dimension to be counted only once when summing. If sum is used directly, the value is multiplied by the number of rows under different dimensions. If sum(distinct) is used, it can only be an aggregate measure and cannot be directly converted to a window function with partition by.

Implementation: similar to count(distinct).
Approach:
The numeric field must first be aggregated by the required dimensions: [订单数]=sum([原始订单量])over(partition by [们店],[产品])
Window steps for distinct sum:\

1. Get the value in each group: [取值]=max([订单数])over(partition by [们店],[产品])\
2. Distinct count for summing: [个数]=size(collect_set([产品])over(partition by [们店]))\
3. Sum after deduplication: [去重求和开窗]=sum([取值]/[个数])over(partition by [们店])
   =sum(max([订单数])over(partition by [们店],[产品])/size(collect_set([产品])over(partition by [们店])))over(partition by [们店])

Note

The calculation logic of this field is: take the maximum order count under the store and product dimensions, divide it by the number of products under the store dimension, and then sum by store dimension. When there is only one dimension, you only need a field that can distinguish different order counts on each row as different values.