Quick calculation of table

1. Overview

1.1 Version

1.2 Application scenario

In order to facilitate users to process calculation data, FineBI provides a quick calculation function for indicator fields.

1.3 Feature introduction

Group table and cross table support fast calculation function, but schedule does not.

Quick calculation is also effective for the total value. When the "Sales Volume" is sorted within the group, the total line is also sorted, as shown in the following figure:

2. YoY/MoM

2.1 Definition

After the field is dragged into the dimension, the indicator field can be quickly calculated.

If the analysis area dimension column contains a "date" field, the calculation types supported by time grouping are as follows:

2.2 The same period

Note: The required dimension fields are year month day, year week, year month, year quarter type.

E.g:

• Year-on-year: The sales in January 2009 are known, and the sales in January of last year (2008) are its year-on-year data.

• Month-on-month: The sales data on February 1 is known, and the sales on January  1 of the previous month are its monthly data.

• Weekly period: Knowing the sales data of this week and Thursday, then the sales of last week and Thursday will be its weekly data.

The example uses "ORDERS" data to calculate the year-on-year data for each quarter.

Drag in "SIGNDATE" in the dimension and change the grouping to "Year Quarter". To facilitate comparison, drag in two "AMOUNT" in the indicator column, and ask for the "year period" for the latter "AMOUNT" field, as shown in the following figure:

Note: Seeking the same period is to calculate the data value of the current time point in the previous time dimension, and the same period data obtained when different time groups are different. When the grouping is "Year Month", the same period of the year is the data of the same month last year; when the grouping is "Year Month Day", the same date of the year is the data of the same day last year.

Effect as shown below:

How to use the function to calculate the same period value:

Formula SAME_PERIOD(SUM_AGG(AMOUNT),"Y")

2.3 Year-on-year growth value

E.g:

• Year-on-year growth value: August 2020 sales-Last year (2019) August sales are its year-on-year growth value data.

It can only be used when the time dimension dragged into the dimension is year month day, year week, year month, year quarter and year.

The example uses "ORDERS" data to calculate the year-on-year growth data for each quarter.

Drag "SIGNDATE" into the dimension and change the grouping to "Year Quarter". To facilitate comparison, drag two "AMOUNT" in the indicator column, and calculate the "Year-on-Year Growth Value" for the latter "AMOUNT" field, as shown in the figure below :

Results as shown below:

2009-1 quarter contract value year-on-year growth value: 1=2-3

Of course, if you drag "SIGNDATE" into the dimension and group it as "Year Month Day", you can set the same period of "Year, Quarter, Month, Week", for example, set the same period data with a monthly cycle, as shown in the following figure:

2.4 Year-on-year growth rate

Year-on-year growth rate = (current period value-same period value) / same period value * 100% = year on year growth value / same period value * 100%

It can only be used when the time dimension dragged into the dimension is year month day, year week, year month, year quarter and year.

1) Click "Quick Calculation>Year-on-year/Month-on-month>Year-on-year growth rate>Year" for AMOUNT, as shown in the figure below:

2) Take the year-on-year growth rate in the 2019-1 quarter as an example, the year-on-year growth rate = (2587700-3142400)/3142400*100%.

3) If the user only needs the year-on-year data for 2008 (the same is true for the quarter-on-quarter), they can be filtered, as shown in the following figure:

2.5 Month on month

Calculate month-on-month means finding the value of the previous cycle relative to the current time granularity. For example, the value corresponding to the day before today and the month before this month is the ring period value.

It can be used only when the time field dragged in the dimension is grouped as "year month day, year week, year month, year quarter and year".

Drag "SIGNDATE" into the dimension, change the grouping to "Year Month", and click "Quick Calculation>Month-on-month" for AMOUNT, as shown in the figure below:

The value of the same period of 2008-02 is AMOUNT of 2008-01, and the effect is shown in the figure below:

How to use the function to calculate the period value:

Formula: PREVIOUS_PERIOD (AMOUNT)

2.6 Chain growth value

It can be used only when the date field dragged in the dimension is grouped as "year month day, year week, year month, year quarter and year".

Drag in "SIGNDATE" in the dimension, change the grouping to "Year Quarter", and click "Quick Calculation>Year-on-year/Month-on-month>Chain growth value" for AMOUNT, as shown in the figure below:

Results as shown below:

1=2-3

2.7 Chain growth rate

Chain growth rate = (current period value-ring period value) / ring period value * 100% = chain growth value / ring period value * 100%

It can only be used when the time dimension dragged into the dimension is year month day, year week, year month, year quarter and year.

Drag in "SIGNDATE" in the dimension, change the grouping to "Year Quarter", click "Quick Calculation>Year-on-year/Month-on-month>Chain growth rate" for AMOUNT, as shown in the following figure:

The effect is shown in the figure below. Taking the month-on-month growth rate in the 2009-1 quarter as an example, the month-on-month growth rate = (2587700-2428900)/2428900*100%.

2.8 Custom the same chain ratio

The analysis area does not include the "date" field, that is, when the dimension column is not a "date" field, and the area to be analyzed has a "date" field, it supports the quick calculation of "contrast value, growth value, growth rate" by customizing the same month-on-month method .

For example, you want to calculate the growth of "AMOUNT" with different "PAYMETHOD" in "SIGNDATE" in January 2008 relative to "AMOUNT" with different "PAYMETHOD" in March 2008.

2.9 Precautions for null value calculation

In the same month-on-month calculation, when the previous period value is empty or the current period value is empty, when the current period value is calculated with the previous period value, the corresponding year-on-year and month-on-month (including growth value and growth rate) calculation results are displayed as empty.

3. Percentage

The current indicator percentage is the percentage of the current fine-grained data to the sum of the fine-grained data of all dimensions in the indicator, and represents the percentage of a single value to the total value of the indicator.

For example, the proportion of credit = 8,315,400/16,448,700*100% as shown in the figure below:

How to use the function to calculate the percentage:

Formula: SUM_AGG(AMOUNT)/TOTAL(SUM_AGG(AMOUNT))

Seeking the proportion in the group is to find the percentage of the current index granularity data to the total grouping.

For example, drag in "CUSTOMERID" and "PAYMETHOD" in the dimension bar, drag in two "AMOUNT" in the indicator bar, and select drop-down> quick calculation> percentage within the group for the last "AMOUNT", as shown in the following figure:

It can be seen that the use of credit with a CUSTOMERID of 1.0 accounts for 37.56% of AMOUNT.

The cross table also calculates the proportion in the group of data grouped by row dimensions. For example: In 2008, CUSTOMERID was 1.0, and credit payments accounted for 31.54% of AMOUNT; in 2009, CUSTOMERID was 1.0, and credit payments accounted for 47.94% of AMOUNT. As shown below:

How to use the function to calculate the proportion in the group:

Formula: SUM_AGG (AMOUNT)/TOTAL(SUM_AGG (AMOUNT), 1)

Seeking ranking is to rank the currently grouped indicators according to their numerical value. Users can choose to rank in ascending order (from small to large) or descending (from large to small).

1) Drag in "CUATOMERID" and "PAYMETHOD" in the dimension bar, drag in two "AMOUNT" in the indicator bar, and select the drop-down of "AMOUNT" behind> Quick Calculation> Ranking> Ascending Ranking. As shown below:

2) In order to see it more clearly, uncheck the "Show total row in the group". It can be seen that in the AMOUNT (Sum-Ascending ranking), the smallest AMOUNT rank is 1 and the largest rank is 21. As shown below:

3) Change the ranking to descending order, so that the ranking with the largest AMOUNT value is 1, as shown in the figure below:

4) When there are the same data, the ranking is the same. For example, when there are two amounts of 8, after setting the descending ranking, there are two rankings of 1 and the amount of 7 are ranked third.

How to use functions to rank:

Formula: RANK_ANLS(SUM_AGG(AMOUNT),0,"desc")

6. Group Ranking

The group ranking  is to calculate the ranking order of the current index value in the group, and there are also two options for ascending ranking and descending ranking.

1) Drag in "CUSTOMERID" and "PAYMETHOD" in the dimension bar, drag in two "AMOUNT" in the indicator bar, and select drop-down for the "AMOUNT" behind> Quick calculation> Group Ranking> descending ranking. As shown below:

2) The effect is shown in the figure below. It can be seen that they are ranked separately in the "CUSTOMERID" group, as shown in the figure below:

How to use the function to calculate the group ranking:

Formula: RANK_ANLS(SUM_AGG(AMOUNT),1,"desc")

7. All values

All values are calculated for all values of the indicator, including sum, average, maximum, and minimum. If you choose to calculate for all values, the values displayed in all dimensions of the indicator are the same, and they are all calculated results.

1) Here, we use the sum of all values as an example. For "AMOUNT", select Drop-down>Quick Calculation>all values>Sum, as shown in the figure below:

2) To be more intuitive, uncheck "Show total line within group" under the component style. The sum of all values is the same as the total value of "AMOUNT(Sum)". As shown below:

How to use functions to calculate all values (sum, average, maximum, minimum):

• "Sum" formula: TOTAL (AMOUNT, 0, "sum")

• "Average" formula: TOTAL (AMOUNT, 0, "avg")

• "Maximum" formula: TOTAL (AMOUNT, 0, "max")

• "Minimum" formula: TOTAL (AMOUNT, 0, "min")

8. All values in group

All values in the group are calculated for all values of the indicator in the group, including sum, average, maximum, and minimum. If you choose to calculate all values in the group, the values displayed in the indicator group are all the same, and they are all calculated results for all data in the group.

1) Here we use all the values in the group as an example. For "AMOUNT", select Drop-down>Quick Calculation>All values in the group>Sum, as shown in the figure below:

The effect is shown in the figure below. You can see that all the values (Sum) in the group are the sum of the grouped data.

How to use functions to calculate all values in the group (sum, average, maximum, minimum):

• "Sum" formula: TOTAL (AMOUNT, 1, "sum")

• "Average" formula: TOTAL (AMOUNT, 1, "avg")

• "Maximum" formula: TOTAL (AMOUNT, 1, "max")

• "Minimum" formula: TOTAL (AMOUNT, 1, "min")

9. Accumulation

The accumulation is the cumulative statistical result of all the values of the indicator, and the indicator values are sequentially accumulated from top to bottom.

1) For the latter "AMOUNT", select drop-down>Quick Calculation>Accumulation, as shown in the figure below:

2) In order to be more intuitive, uncheck the "Show total line within group" under the component style, and the effect is shown in the following figure:

How to use the function to calculate the accumulation:

Formula: ACC_SUM(SUM_AGG(AMOUNT),0)

10. Accumulation within the group

Accumulation within the group is the cumulative statistical result of all the values in the indicator group, and the indicator values are sequentially accumulated in the group from top to bottom.

For the latter "AMOUNT", select drop-down>Quick Calculation>Accumulation within group. As shown below:

How to use the function to calculate the accumulation within the group:

Formula: ACC_SUM(SUM_AGG(AMOUNT),1)

The current dimension percentage is the percentage of the current fine-grained data to the sum of all fine-grained data of the dimension, and represents the proportion of a single value to the total value of the current dimension.

1) For example, drag "AMOUNT" and "PAID" in the indicator bar, as shown in the figure below:

2) The effect is shown in the figure below, the current dimensionality percentage of AMOUNT=AMOUNT(Sum)/(AMOUNT(Sum)+PAID(Sum))