Infinity/negative/null values are displayed as 0

1.1 Expected effect

Many users now have data tables with various special values, such as negative numbers or calculated ∞. In this case, you can use FineBI to convert the special values to the data you want to render.

Example 1: Show all ∞ as 0 after division calculation.

Average Sales = Sales / Customer Service, if the number of customers being divided is 0, the output will be displayed as 0 instead of ∞, as shown in the figure below.

Example 2: Change the null value to 0 and for the percentage of expenses to revenue after the null value is displayed as 0, as shown in the following figure.

Example 3: Display a negative number as 0 and then sum the data columns as shown in the following figure.

1.2 Implementation idea

Example 1: Use IF function, if the divisor is 0, then output 0 directly, otherwise output the result after division.

Example 2: Use IF function, if the value is empty, then display 0, otherwise output the result after division.

Example 3: Use SIGN function, if the data is positive = 1, then output the data, otherwise output 0.

Example data: Sales.xlsx

2.1 Upload data

Upload the sample data to FineBI as shown below.

2.2 Create dashboard

Select "Dashboard/New Dashboard", set the creation location, and click "OK", create the component and select the "Sales" data table, click "OK", as shown in the following figure.

2.3 Add calculation indicator

Click "+" to add the calculation indicator, name it "Average Sales", enter the calculation formulaIF(SUM_AGG(People)=0,0,SUM_AGG(Sales)/SUM_AGG(People))and click "OK", as shown in the following figure.

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.

2.4 Effect view

Drag and drop the required fields into the corresponding analysis area, see section 1.1 of this document for details.

Example data: data.xlsx

3.1 Upload data

Upload the sample data to FineBI as shown below.

3.2 Create dashboard

Select "Dashboard/New Dashboard", set the creation location and click "OK", create the component and select the "Data" table, click "OK", as shown in the following figure.

3.3 Add calculation indicator

Click "+" to add the calculation indicator, name it "outcome percentage", enter the calculation formula IF(SUM_AGG(outcome)=null,0,SUM_AGG(outcome)/SUM_AGG(income)), click "OK", as shown in the figure below.

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.

3.4 Effect view

Drag and drop the required fields into the corresponding analysis area, see section 1.1 of this document for details.

Example data: Profit.xlsx

4.1 Upload data

Upload the sample data to FineBI as shown below.

4.2 Create dashboard

Select "Dashboard/New Dashboard", set the creation location and click "OK", create the component and select the " Profit" data table, click "OK", as shown in the following figure.

4.3 Add calculation indicator

4.3.1 Display negative numbers as 0

Click "+" to add the calculation indicator, name it "Negative to 0", enter the calculation formula IF(SIGN(Profit)=1,Profit,0), and click "OK", as shown below.

4.3.2 Sum over all positive numbers

Click "+" to add the calculation indicator, name it "Positive Profit", enter the calculation formulaTOTAL(SUM_AGG(IF(SIGN(Profit)=1,Profit,0)),0,"sum"), and click "OK", as shown in the following figure.

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.

4.4 Effect view

Drag and drop the required fields into the corresponding analysis area, see section 1.1 of this document for details.