EXAMPLE - KTHLARGEST Function

This example explores how you can use aggregation functions to calculate rank of values in a column using the `KTHLARGEST` and `KTHLARGESTUNIQUE` functions.

Source:

You have a set of student test scores:

StudentScore
Anna84
Ben71
Caleb76
Danielle87
Evan85
Faith92
Gabe87
Hannah99
Ian73
Jane68

Transformation:

You can use the following transformations to extract the 1st through 4th-ranked scores on the test:

Transformation Name `New formula` `Single row formula` `KTHLARGEST(Score, 1)` `'1st'`

Transformation Name `New formula` `Single row formula` `KTHLARGEST(Score, 2)` `'2nd'`

Transformation Name `New formula` `Single row formula` `KTHLARGEST(Score, 3)` `'3rd'`

Transformation Name `New formula` `Single row formula` `KTHLARGEST(Score, 4)` `'4th'`

Transformation Name `New formula` `Single row formula` `KTHLARGESTUNIQUE(Score, 3)` `'3rdUnique'`

Transformation Name `New formula` `Single row formula` `KTHLARGESTUNIQUE(Score, 4)` `'4thUnique'`

Results:

When you reorganize the columns, the dataset might look like the following:

StudentScore1st2nd3rd4th3rdUnique4thUnique
Anna84999287878785
Ben71999287878785
Caleb76999287878785
Danielle87999287878785
Evan85999287878785
Faith92999287878785
Gabe87999287878785
Hannah99999287878785
Ian73999287878785
Jane68999287878785

Notes:

• The value `87` is both the third and fourth scores.
• For the `KTHLARGEST` function, it is the output for the third and fourth ranking.
• For the `KTHLARGESTUNIQUE` function, it is the output for the third ranking only.
[{ "type": "thumb-down", "id": "hardToUnderstand", "label":"Hard to understand" },{ "type": "thumb-down", "id": "incorrectInformationOrSampleCode", "label":"Incorrect information or sample code" },{ "type": "thumb-down", "id": "missingTheInformationSamplesINeed", "label":"Missing the information/samples I need" },{ "type": "thumb-down", "id": "otherDown", "label":"Other" }]
[{ "type": "thumb-up", "id": "easyToUnderstand", "label":"Easy to understand" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"Solved my problem" },{ "type": "thumb-up", "id": "otherUp", "label":"Other" }]