Implementation Method for Using Arrows to Show Growth Trends in Tables

Background Introduction

Traditionally, when analyzing data growth trends (such as year-over-year and month-over-month) in Excel tables, many people are accustomed to using upward or downward arrows and different colors to represent data change trends. When making table reports using BI, many users also hope to follow this habit. So how to achieve this effect in Guandata BI?

Implementation Methods

There are three implementation methods. The demonstration case uses year-over-year and month-over-month calculation as an example. Please choose the appropriate method according to your needs.

Method One: Simplest Method

1. Use the advanced function "Year-over-Year and Month-over-Month" in the table to calculate comparison values, growth values, and growth rates as needed. Arrows need a separate field, so you need to drag in one more field to get the year-over-year/month-over-month growth rate.

Note: All calculated values above use the same numerical field, which will display the same column name. You can set aliases to distinguish them.

2. Click on one of the growth rate fields (already named "Trend"), select "Set Conditional Format" from the menu, create a new column rule. Set the field value range to less than or equal to 0, then "Replace Data" select "Replace with Symbol", select downward arrow, set the font color to green in the font bar, click OK to save. Then create another column rule to set red upward arrow. If the table displays subtotals and totals, you can check "Apply to Subtotals and Totals" and save.

3. If other fields need to set color styles according to the same rules, refer to the previous step method to create column rules, keep the "Replace Data" part as "None". The final display effect is shown in the figure below.

Method Two: Good Effect but Complex Method (Requires Familiarity with SQL Writing)

1. Create new calculated fields to separately calculate current period and comparison time range data. For example, the figure below creates 4 calculated fields, respectively calculating 2017 and 2018 sales amounts, arrows, and using functions to concatenate arrows with growth values and growth rates into one field. Growth values and growth rates need to directly specify format and precision in the formula using functions.

2. Drag the above newly created fields into the numerical bar as needed, click on the "Month-over-Month" field to set conditional format. Since newly created aggregated metrics are all treated as numerical fields by default, when setting rules, you can't select "Contains" relationship, you need to click "More" in the bottom left corner to manually select the "Arrow" field created in the previous step, set the field equal to ↑, then set the arrow color and confirm save. Set both upward and downward arrows and save.

The final display effect is shown in the figure below.

Method Three: Exactly Like Excel Method: Applicable Prerequisite is BI Has Complex Report Function Enabled (This Function is a Value-Added Service)

1. Create a new complex report, in the data preparation interface, drag in fields and use the advanced calculation function "Year-over-Year and Month-over-Month" to calculate year-over-year and month-over-month data.

2. Create an Excel template locally, set up headers, cell formats, and fill formulas. Set the field format for the month-over-month growth rate column to percentage, and set conditional format as shown in the figure below.

3. Save Excel, upload to BI and save. The display effect is as follows.