-
Type: Improvement
-
Status: Closed
-
Priority: Major
-
Resolution: Unresolved
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: Data Aggregation
-
Labels:None
Use case: Display top 4 countries by number of sales, with aggregate sales amount and number of account responsibles per country.
This requires aggregation on two different levels:
- per distinct account responsible per country
- per country
This can be achieved with a concat transformation inside groupby
$apply=groupby((Country),concat( groupby((AccountResponsible))/aggregate($count as number_of_leaves), aggregate(SalesNumber,SalesAmount) )) /orderby(SalesNumber desc)/top(4)
that returns two rows per country
Country | number_of_leaves | SalesNumber | SalesAmount |
---|---|---|---|
DE | 2000 | ||
DE | 500 | 5000 | |
UK | 1500 | ||
UK | 400 | 5500 |
But then the subsequent orderby cannot sort the countries, because each country occupies two rows. Before the orderby, the table would need to be compressed into
Country | number_of_leaves | SalesNumber | SalesAmount |
---|---|---|---|
DE | 2000 | 500 | 5000 |
UK | 1500 | 400 | 5500 |
One way to achieve this is
$apply=groupby((Country),concat( groupby((AccountResponsible))/aggregate($count as number_of_leaves), aggregate(SalesNumber,SalesAmount) )/aggregate( number_of_leaves with max as number_of_leaves2, SalesNumber with max as SalesNumber2, SalesAmount with max as SalesAmount2 )) /orderby(SalesNumber2 desc)/top(4)
But this is cumbersome and requires renaming the properties.
Can [OData-Aggr] offer a more elegant solution?