Hi,
I'm trying to find a recommended product, based on the following criteria:
1. The product should be the most frequently occurring in the dataset
2. If there is a tie in the product names, then we should display product with highest margin.
For example in this sample dataset:
Table:
Load * inline
[
Product,Margin
x,.10
x,.20
x,.30
x,.40
y,.50
y,.60
y,.70
y,.80
a,.85
z,.95
z,.90
z,.98
];
X and Y are most frequently occurring, but we need to display Y as recommended product as it has highest margin of 80%
I'm able to find the maximum occurrences using this function, but not able to return product with highest margin.
max(aggr(count(Product),Product))
I'm struggling with the Firstsortedvalue function.
It would be great if anyone can provide some help on this.
Thanks!
