Skip to main content
Announcements
New: No-code data prep in Qlik Cloud Analytics™ TAKE A TOUR
cancel
Showing results for 
Search instead for 
Did you mean: 
GeorgeEard
Contributor II
Contributor II

Scores based on Quartile

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.

 

Labels (2)
1 Solution

Accepted Solutions
Clement15
Partner - Specialist
Partner - Specialist

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;

 

View solution in original post

1 Reply
Clement15
Partner - Specialist
Partner - Specialist

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;