Cross-table Detail Calculation Scenario

Version

Application Scenario

The cross-table detail calculation capability is improved in FineBI 7.0. The cross-table calculation on the row level can satisfy the requirements of more calculation scenarios without being prevented by the error message "Non-aggregation calculation cannot be performed across tables."

Some cross-table detail calculation scenarios are listed as follows:

• Obtaining Sales Amount through multiplying Unit Price in Product Dimension Table by Sales Quantity in Sales Table

• Detail filtering or conditional filtering of indicators across tables

• Calculating derived indicators, for example, calculating the sales amount when the filtering condition is that the year is 2022 and the product type is A

Cross-table Detail Calculation Logic

The current capabilities of cross-table calculation for overall detailed data aggregation are summarized as follows:

Left Table (Baseline Table)	Analysis Direction	Right Table	Method to Perform Detail Calculation with the Left Table as the Baseline Table	Data Merging Method
1	→	N	def(sum(Indicator of the right table)),[Dimension of the left table], + Field of the left table	Left join the left table with the right table.
N	→	1	def(sum(Indicator of the right table)),[Dimension of the left table], + Field of the left table	Left join the left table with the right table.
N	→	N	def(sum(Indicator of the right table)),[Dimension of the left table], + Field of the left table	Left join the left table with the right table.
1	←	N	Note: Not supported currently You are advised to join the left table to the right table and analyze data based on the analysis direction.	Detail data will be bloated.
N	←	1	The left table   + The right table	Left join the left table with the right table.
N	←	N	Note: Not supported currently You are advised to join the left table to the right table and analyze data based on the analysis direction.	Detail data will be bloated.
1	←→	1	def(sum(Indicator of the right table)),[Dimension of the left table], + Field of the left table The left table   + The right table	Left join the left table with the right table. Fully join the left table to the right table.

What is the baseline table?

• In DEF calculations, the fields in both Table A and Table B are calculated. The table where the analysis dimension is finally used for calculation is considered the baseline table.

• In scenarios where DEF functions are not used, the N-end table is the baseline table. For example, here is a formula Unit Price (in Product Dimension Table) * Sales Volume (in Sales Details Table). Sales Details Table where the Sales Volume field exists is the baseline table.

Application Scope

• Cross-table detail calculation only supports the 1-to-N association. Whether the association is direct or not, cross-table detail calculation is only available for tables in the analysis direction. In other words, the current table can only be joined to the 1-end tables that are analyzable from the current table.

• Cross-table detail calculation in the analysis subject is not supported.

Scenario description

You may want to calculate the sales amount of each product. To calculate Sales Amount, you can use the formula Unit Price * Sales Volume.

The data model is as follows: The Unit Price and Sales Volume fields are in two tables, respectively. The Product ID field is used as the key to associate the two tables.

Implementation Method

Formula:

To calculate the sales amount of each product, you can multiply the sales quantity of each product by the unit price or use the formula DEF_ADD(SUM_AGG(Sales Volume),Product ID) * Unit Price.

The Product ID field belongs to the baseline table Drug Dimension Table.

In a manufacturing production scenario, you can analyze the planned and actual delivery quantities. The following figure shows the data model and table structure.

Calculating the Deviation of Each Material

To calculate the deviation between planned and actual quantities for each material, you can subtract the actual delivery quantity from the planned delivery quantity of each product or use the formula DEF(SUM_AGG(Planned Delivery Quantity),Material ID) - DEF(SUM_AGG(Actual Delivery Quantity,Material ID).  #The field Material ID belongs to Product Line Table, which is the baseline table.

Calculating the Forecast Accuracy (Extended)

In FineBI 7.0, calculation capabilities are enhanced, enabling flexible integration of shared dimensional detail fields into calculations. For details about the implementation scenario, see the calculation formula of weights.

To calculate Forecast Accuracy Calculation, you can use the formula 1-SUM_AGG(Deviation Rate * Weight).

• To calculate Deviation Rate, you can use the formula (Planned Delivery Quantity - Actual Delivery Quantity)/Actual Delivery Quantity for the material of each product line at a certain time.

• To calculate Weight, you can use the formula Delivery quantity of material of each product line at a certain time/Delivery quantity of each product line at a certain time.

Implementation method

1. To calculate Deviation Rate, you can use the formula DEF((SUM_AGG(Planned Delivery Quantity) - SUM_AGG(Actual Delivery Quantity))/SUM_AGG(Actual Delivery Quantity),[Product Line Name,Material Name,Date]).

2. To calculate Weight, you can use the formula DEF(SUM_AGG(Actual Delivery Quantity),[Product Line Name,Material Name,Date])  #Delivery quantity of the material of each product line at a certain time#/DEF(SUM_AGG(Actual Delivery Quantity),[Product Line Name,Date])  #Delivery quantity of each product line at a certain time#

Scenario description

You may want to add cross-table detail filtering conditions for Sales Amount. The following figure shows the data model and table structure.

Implementation method

You can add the Sales Amount indicator with the filtering condition that the province is not Jiangsu and the product category is not category A. In this case, the corresponding detail data will be obtained by filtering.