Custom expressions in custom sets

Report level expressions, custom expressions, are handled as post-aggregation, logical layer, calculations in Visualizer reports unless that same report is employed as a custom set within a Visualizer report. Custom sets that employ custom expressions and are employed in another Visualizer report are treated as pre-aggregation level calculations that run at the database level like custom measures.

For example, two visualizer reports have been created that utilize pre-aggregation and post-aggregation calculations inserted into a dashboard for easy side-by-side comparison. One report uses the Custom Measure Sales which is calculated at the database level or what we call pre-aggregation. The other report uses Revenue that is created by an expression in the Visualizer BQL editor using the following formula, (Quantity * Average unit price). The difference in calculations is because Sales is calculated by multiplying every quantity by its actual price at the time of sale and Revenue is using the average unit price and multiplying by the total aggregated quantity. This is not as accurate:

A Top 10 result and descending sort has been applied that returns not only different measured values but a different result set for the top 10 Customers. In the Sales (pre-aggregation) based report, Lehmans Marketstand is the 10^th ranked customer and in the Revenue (post-aggregation) report, Lehmans Marketstand is the 6th ranked customer. Additionally, Hanari Carnes is ranked 8^th in the Sales based report and not listed at all in the Revenue based report which has been replaced by the Hillarion-Abastos Customer in the 10^th ranked spot.

If the Revenue (post-aggregation) report is saved as a Custom Set and apply it to another report as a filter, then the result set would be different than the one listed.Birst sends the custom expression calculation for revenue in the custom set to the database rather than applying it post-aggregation in the logical layer.

For example a report uses the custom set, based on the revenue defined by the custom expression, but now the results are identical to the report built using the custom measure, Sales (pre-aggregation). Lehman's is ranked 10^th and Hanari Carnes returns to the result set. The measure values were also calculated identically this time because the expression was applied to every row of data in the database just like the sales custom measure.