Type: New Feature
Affects Version/s: V4.0_CS02
Fix Version/s: V4.0_CSD04
Component/s: Data Aggregation
It is interesting to get the ratio between an individual and an aggregated value from the same input set.
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:
groupby((Customer),aggregate(Amount with sum as CustomerAmount))
/ratio(CustomerAmount to CustomerAmount with sum as RevenueContribution)
groupby((Customer,Year) aggregate(Amount with sum as CustomerYearAmount))
/groupby((Customer),ratio(CustomerYearAmount to CustomerYearAmount with sum as RevenueTrend)
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).