topic Re: Finding 3 largest values out of a set in Discussions

Finding 3 largest values out of a set

BHarris — Tue, 31 Oct 2023 05:42:30 GMT

I need to identify the 3 largest values out of a set of data grouping by certain columns, e.g. with Big Class, suppose I want to identify the 3 tallest students at each age for each sex. I don't think there are any ties/ambiguity in that set, but in my set there are, e.g. if the heights for a given age/sex combination were (64, 63, 61, 61, 61, 61), it doesn't matter which of the 61s is selected.

Is there a formula I could use in a column that would return 1 for these identified students and 0 for the rest?

I imagine there's a way to do it with .jsl loops, but I'm hoping there's a formula that will do this.

Re: Finding 3 largest values out of a set

jthi — Tue, 31 Oct 2023 05:59:17 GMT

You can use formula for this. Sometimes just Col Rank() is enough but sometimes you should combine it with Col Number(). After that use simple comparison to get 1 or 0 value

1 + Col Number(:height, :age, :sex) - Col Rank(:height, :age, :sex) < 4

Re: Finding 3 largest values out of a set

BHarris — Wed, 01 Nov 2023 05:15:33 GMT

Works perfectly, exactly what I needed. Thanks @jthi!