Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sheetalp
New Contributor

How to compute mode in multimodal dataset based on max value of a measure?

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!

1 Reply

Re: How to compute mode in multimodal dataset based on max value of a measure?

Try this

=FirstSortedValue(Product, -Aggr(Count(Product) + (Max(Margin)/1E10), Product))

Community Browser