cancel
Showing results for 
Search instead for 
Did you mean: 
suxinji
Employee
Employee

Overview

In business analytics, you may run into the cases that require aggregation at different levels of details. For example, in the product sales business, the price discount may vary depending on geographic area, business unit, product category, sales channel, etc.  To understand the factors that may impact the discount given, you can get basic statistics about discounts at each level and compare them.

Incorta has a variety of level based aggregate solutions.  In the solution we discuss in this article, you can calculate the level-based measure using an Incorta Aggregate Table Insight or create it as an Analyzer Table in the schema.

In another article, we will show you how to create an Incorta Materialized View using Spark SQL to perform the same level-based aggregate.

Solution

Go to Schema -> +New -> Select Incorta Analyzer

suxinji_0-1651689474074.png

Select Aggregated Table

Here is an example. In this case, you want to know different levels of discount given by sales and for each level, what the row count, minimum discount, maximum discount, average discount, and median discount are.

suxinji_2-1651690856649.png

You can use Incorta formulas: count(), groupBy(), min(), max(), average(), and median(). The groupBy() input allows you to define the level for which the aggregate will be calculated.

median(
	RevenueRecognition.RawTransaction.DISCOUNT_RATE, 
	groupBy(
		RevenueRecognition.RawTransaction.DE_AREA, 
		RevenueRecognition.RawTransaction.BU, 
		RevenueRecognition.RawTransaction.SUB_BU, 
		RevenueRecognition.RawTransaction.SALES_COVERAGE_CODE, 
		RevenueRecognition.RawTransaction.ATTRIBUTE_CD, 
		RevenueRecognition.RawTransaction.SOFTWARE_USAGE_CD
	)
)
suxinji_3-1651691437103.png

Note:

The hierarchy levels are implicitly defined via the grouping attributes (dimension) defined for the aggregated table.  The order of columns listed in the groupBy() function has to match the order of the columns mentioned in the grouping attributes but can be a subset of these attributes.

Contributors
Version history
Last update:
‎05-11-2022 08:27 AM
Updated by: