Scores based on Quartile

[GeorgeEard]

Hi, 

I need to create a system where partners are scored based on what quartile they are in, in regards to GP%.

For example the 4th quartile of GP% would get 20 points, 3rd 15 points, 2nd 10 points for example.

So if JD sports' average GP% is in the 4th quartile, JD sports will get 20 points.

Thank you.

[Clement15 · Accepted solution]

Hello,

There is certainly a way to optimize it but here is a first solution to your need

Test:

Load *

Inline [

Dim,GP

JD,2

JD,4

JD,8

AB,3

AB,2

AB,6

CE,1

CE,4

CE,30

PO,3

PO,9

PO,7

];







left join(Test)

TEST2:

Load

1 as TEMP,

Dim,

avg(GP) as AVG_GP

Resident Test

group by Dim;




NoConcatenate




TEST3:

LOad 

TEMP,

Fractile(AVG_GP,0.25) as Fractile_25,

Fractile(AVG_GP,0.50) as Fractile_50,

Fractile(AVG_GP,0.75) as Fractile_75

Resident Test

group by TEMP

;




let vFractile_25 = peek('Fractile_25',-1,'TEST3');

let vFractile_50 = peek('Fractile_50',-1,'TEST3');

let vFractile_75 = peek('Fractile_75',-1,'TEST3');







NoConcatenate




Final:

Load

Dim,

GP,

AVG_GP,

if(AVG_GP <= $(vFractile_25), 20,

 if(AVG_GP <= $(vFractile_50), 15,

    if(AVG_GP <= $(vFractile_75), 10,

        5))) as Fractile

Resident Test;




Drop Tables Test,TEST3;