Show highest and lowest Products based on %

[qlikwiz123]

I need to show Highest Product name and lowest product name with respective % values in a Text Box.

I am using the expression below but for some reason, it gives me a blank value.

Highest:

=FirstSortedValue([Product], -Aggr((Count(Distinct {$<[Group]={'Elite'}>} ID)/
Count(Distinct(ID))), [Product ]))

Lowest:

=FirstSortedValue([Product], Aggr((Count(Distinct {$<[Group]={'Elite'}>} ID)/
Count(Distinct(ID))), [Product ]))

Not sure what is wrong with these expressions. Also, if I want to show these High and low Products only if they satisfy a condition (Sum(Items)>100), how do I include this condition in the expression?

[KGalloway]

Would something like the following work?

Formula for highest product: FirstSortedValue([Product], -Aggr((Count(Distinct {$<[Group]={'Elite'}>} ID)/Count(total ID)), [Product]))

Formula for lowest product: FirstSortedValue([Product], Aggr((Count(Distinct {$<[Group]={'Elite'}>} ID)/Count(total ID)), [Product]))

If not, could you provide some sample data and more details on the type of grouping you want to do to find the highest and lowest product?

[vinieme12 · Accepted solution]

for Part 1 of your query as @KGalloway  suggested, replace count(Distinct (ID) )  with Count(Distinct Total ID )

Also, if I want to show these High and low Products only if they satisfy a condition (Sum(Items)>100)

for part 2, add set analysis to FirstSortedValue()  , highlighted

=FirstSortedValue({<[Product]={"=sum(Items)>100"}>}[Product], -Aggr(Count(Distinct {$<[Group]={'Elite'}>} ID)/Count(Distinct Total ID)  , [Product ])  )

[qlikwiz123]

Thank you @vinieme12 

=FirstSortedValue({<[Product]={"=sum(Items)>100"}>}[Product], -Aggr(Count(Distinct {$<[Group]={'Elite'}>} ID)/Count(Distinct Total ID)  , [Product ])  )

What happens if I have more than one Product value which satisfies the above condition?

For example, if Product A and B both have Sum(Items)>100 and have the same Output of 50? Looks like it is picking up the first value that satisfies Sum(Items)>100. In this case A has Sum(Items) as 250 and B has Sum(Items) as 1000, it is showing 'A'.

How do I show B instead of A, since it has more Sum(Items)?

[vinieme12]

use a sorted aggr()

refer the example here

=Aggr(
    <Expression> ,
    (Product,(=Sum({1} Items ),Desc))
    )

https://community.qlik.com/t5/Design/Recipe-for-a-Pareto-Analysis-Revisited/ba-p/1473684

https://community.qlik.com/t5/Design/The-sortable-Aggr-function-is-finally-here/bc-p/1470312