Best Practices for Handling Aggregated Expressions in Pivot Grid Widget

Bold BI offers support for various function to set up conditional expressions and load the pivot grid widget. However, there are certain limitations and unsupported expression functions when it comes to calculating the Subtotal and Grand total based on the expression condition. This article aims to provide comprehensive information on the limitations and supported function in the pivot grid grand total with expressions.

Limitations in the pivot grid while creating Expression

The pivot grid is a powerful tool for summarizing and analyzing data, but it comes with certain limitations that you should be aware of to effectively use it in your data analysis workflows. Here are two important limitations to consider:

1. Creating Nested Expression Fields: The configuration of expression fields in the pivot grid includes the use of nested expressions, where one expression field is created by another. In such cases, the grand total in the pivot grid is merely summarized and not calculated according to the expression conditions of the nested expressions.
2. Creating an Expression with Unsupported function: The pivot grid widget will display the message “No records to be displayed” when the configuration of expression fields includes the utilization of unsupported function.
3. Creating an Expression Fields Without function: It is important to note that a limitation exists when configuring expression fields without specifying an aggregation type. The calculation of the grand total in the pivot grid is dependent on the chosen function type in the expression field’s. Hence, it is crucial to create expression fields with suitable function if the intention is to calculate the grand total based on the expression conditions.

In summary, understanding these limitations in the pivot grid is crucial for data analysts and professionals working with data summarization and analysis tools. To effectively use the pivot grid and obtain accurate results in your data analysis tasks, it is important to avoid nested expressions, refrain from using unsupported functions, and ensure that expression fields have appropriate functions.

Handling Expressions in Pivot Grid

The pivot grid widget offers tremendous flexibility for data analysis. We can utilize the mentioned functions to establish a conditional expression and load the pivot grid widget based on expression. For instance, the expression condition is as follows:

Creating Nested Expression

• If you create an expression that is nested within another expression, the subtotals and grand totals will not be calculated based on this expression condition. Instead, they will be summarized using default function methods such as “SUM.”
  
  

Creating an Expression with Unsupported Expression Function

• RUNNINGSUM(SUM([Quantity])) : If you use an expression with unsupported functions, the pivot grid will show “No records to be displayed”. Hence, refrain from using unsupported expression functions when creating an expression.
  
  

Creating an Expression Without Function

• ([Discount] / [Quantity]) * 100 : When an expression without function is used, the pivot grid will be rendered based on this condition for individual cells. However, subtotals and grand totals will not be calculated based on this expression condition. Instead, they will be summarized using default function methods like “SUM.” This distinction is crucial to ensure that your subtotals and grand totals accurately reflect your data.
  
  

Creating an Expression with Supported Functions

• (SUM([Discount]) / SUM([Quantity])) * 100 : We recommend using the supported function to calculate a value based on the expression condition. The pivot grid will calculate this value for each data cell, and subtotals and grand totals will also be calculated using this expression condition. This approach enables you to gain insights into your data while maintaining consistency throughout your analysis.
• For more information on supporting expression functions in the pivot grid, refer to the knowledge articles: Supported Expression Functions