-
Type:
New Feature
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: V4.0_CS02
-
Fix Version/s: V4.0_CSD04
-
Component/s: Data Aggregation
-
Labels:
It is interesting to get the ratio between an individual and an aggregated value from the same input set.
Examples:
1. Customer classification: Revenue per customer in relation to total revenue
2. Revenue trend: Revenue per year in relation to total revenue for any given customer
3. Order size: Order volume relative to average order volume
They cannot be expressed with combining groupby/aggregate, because the individual entity values are no longer available after these transformations have produced an aggregated value.
The ratio transformation takes a comma-separated list of one or more parameters describing a value, an aggregate expression, and an alias:
ratio(valueExpression to aggregateExpression as alias, …)
A valueExpression may be an expression valid in a $filter system query option on the input set that results in a simple value. The aggregateExpression is an aggregate expression as defined for transformation aggregate (section 3.1). The alias introduces a dynamic property for the ratio values in the result set that are of type Edm.Decimal with unspecified precision and variable scale.
The transformation first determines the aggregated value for the input set by applying the aggregateExpression. Then, it calculates for every entity in the input set the ratio between the valueExpression and the aggregated value and adds the result as dynamic property to the entity that is put into the result set.
Applied to examples:
Re 1:
GET ~/Sales?$apply=
groupby((Customer),aggregate(Amount with sum as CustomerAmount))
/ratio(CustomerAmount to CustomerAmount with sum as RevenueContribution)
Re 2:
GET ~/Sales?$apply=
groupby((Customer,Year) aggregate(Amount with sum as CustomerYearAmount))
/groupby((Customer),ratio(CustomerYearAmount to CustomerYearAmount with sum as RevenueTrend)
Re 3:
GET ~/Sales?$apply=ratio(Amount to Amount with average as RelativeOrderSize)
In all these example, the value expression is identical with the base in the aggregate expression, that is, they follow the pattern: ratio(X to X with M as A). If there were no other use cases, the syntax could be simplified to: ratio(X with M as A).
Calculation of ratios now covered by the more general feature described in ODATA-1244.
With this, example 1 could be rewritten as:
GET ~/Sales?$apply= groupby((Customer),aggregate(Amount with sum as CustomerAmount)) /compute(CustomerAmount div aggregate(CustomerAmount with sum) as RevenueContribution)
Closed with no action 2018-11-15.
Discussed 2018-6-21.
Agree that the functionality is very useful.
Observed that ratio represents a special combination of aggregate and compute, and there may be a more generic way to express that relationship. Hubert suggested there may be a way to combine with hierarchies.
ability to refer to the input set
Agreed to defer until we have more use cases and a clearer understanding.