Using Fractile/Percentile function to score representatives.

[MassicotPSCU]

Hi, 

I have sales data table, where records are added for the amount of sales each representative has by fiscal year. While using the sum of sales for all representatives combined, I need to find out where each representative's individual sales lands percentile wise in comparison to that sum of sales.  I'm looking specifically for the 20th percentile, 40th percentile, 60th percentile and 80th percentile.  

Once I find out which percentile each representative belongs to, then I need to do an if statement to give it a score from 1-5. If the representative is lower than the 20th percentile a score of 1 is given. If the representative is lower than the 40th percentile but higher than the 20th percentile a score of 2 is given, and so on.  

Here is what I have so far in my script.

Load
"Rep",
"sales",
"FiscalYear",
if("sales" <= Fractile( TOTAL "sales", 0.2),1,
if("sales" <= Fractile(TOTAL "sales", 0.4),2,
if("sales" <= Fractile(TOTAL "sales", 0.6),3,
if("sales" <= Fractile(TOTAL "sales", 0.8),4,5)))) as Sales_Score

Attached is my sample data as well as my desired output.

[KGalloway]

Hi! I used the following code in the load script:

"""

raw_data:
LOAD
Representative,
Sales,
"Fiscal Year"
FROM [your data source]
(ooxml, embedded labels, table is [Sales Data]);

join

load
"Fiscal Year",
fractile(Sales, 0.2) as fractile_20%,
fractile(Sales, 0.4) as fractile_40%,
fractile(Sales, 0.6) as fractile_60%,
fractile(Sales, 0.8) as fractile_80%
resident raw_data
group by "Fiscal Year";

NoConcatenate
final_data:
LOAD
Representative,
Sales,
"Fiscal Year",
if(Sales <= fractile_20%,1,
if(Sales <= fractile_40%,2,
if(Sales <= fractile_60%,3,
if(Sales <= fractile_80%,4,5)))) as Sales_Score
resident raw_data;

drop table raw_data;

"""

and got this table as the result:

Is this what you are looking for?

[MassicotPSCU · Accepted solution]

Worked like a charm! Thank you so much for this!