Fetching field values for given set of conditions

[#1]

Hi All,

I have a following kind of data.

Id	Name	PercentPass
1	R1	8.4
2	R1	8.5
3	R1	8.8
4	R1	8.7
1	R2	7.8
2	R2	7.9
3	R2	7.4
4	R2	7.5
1	R3	8.5
2	R3	5.8
3	R3	8.3
4	R3	8.2

Name and ID form the composite key.

I trying to achieve the following calculation:

• First find maximum PercentPass Name wise
  • In this case, it would be : R1 - 8.8, R2 - 7.9, R3 - 8.5
• Now store that Name in a variable Name_max having maximum PercentPass
  • In this case, it would be R1
• Store Name in variable Name_max_2 having second maximum PercentPass
  • In this case, it would be R3

I am not able to figure a way to do this.

Please help.

Thanks in advance.

Regards,

Disha Ahuja

[Anonymous]

try like this for Maximum?

=max(Aggr(max(PercentPass),Name))

[sunny_talwar · Accepted solution]

Try these:

Name_max

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name))

Name_max_2

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name), 2)

Name_max_3

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name), 3)

[#4]

Thanks a lot for your help Sunny. This did me wonders!!