Hi,
I have gotten an answer for this in a previous post that worked really well. However, the answer required utilizing the load script, which at the time I thought was the best option. After digging a little deeper I found out that using the load script is not suitable for my current data model. Moving forward, I'm hoping to replicate what I currently have in the load script but as a measure in the sheets.
The data I have:
I have a sales data table, where records are added for the number of sales each representative has for each given date. I also have a calendar table, in which my sales table is linked to by date. I'm using this calendar to get a Month-Year field and a Fiscal Year field.
The data I want:
I need to first get the sum of sales for each representative by month. Then I need to find out where each representative's individual monthly sales lands percentile wise in comparison to the monthly sum of sales for all representatives. 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.
What I've tried as a measure:
Here is what I tried already as a measure, although I do not have the sales grouped by month. Another problem I see with this is that whenever I filter by say 3 representatives, the percentile and scores recalculate for just does 3 representatives. For the purposes of my project, I need the percentiles and scores to be calculated using all representatives by each month and stay the same no matter the selections. I was considering set analysis but I'm not sure if that would work?
if(aggr(Sum(=(sales),Rep) <= fractile(TOTAL aggr(Sum(sales),Rep), 0.20), 5,
if(aggr(Sum(sales),Rep) <= fractile(TOTAL aggr(Sum(sales),Rep), 0.40), 4,
if(aggr(Sum(sales),Rep) <= fractile(TOTAL aggr(Sum(sales),Rep), 0.60), 3,
if(aggr(Sum(sales),Rep) <= fractile(TOTAL aggr(Sum(sales),Rep), 0.80), 2, 1))))
What I want as a measure but have in the load script:
Here is what I was able to create in the load script and it does exactly what I want, however creating an entirely new table is not appropriate for my current data model.
[Calculations]:
Load
"Rep",
sum("sales") as sumsales,
count(sales) as countsales,
"FiscalMonth"
// if("sales" <= Fractile( TOTAL "sales", 0.2),5,
// 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
Resident Temp_Calculation
Where not IsNull("Rep")
group by "Rep","FiscalMonth";
Drop table Temp_Calculation;
join
load
"FiscalMonth",
fractile(sumsales, 0.2) as fractile_20%,
fractile(sumsales, 0.4) as fractile_40%,
fractile(sumsales, 0.6) as fractile_60%,
fractile(sumsales, 0.8) as fractile_80%,
fractile(countsales, 0.2) as countfractile_20%,
fractile(countsales, 0.4) as countfractile_40%,
fractile(countsales, 0.6) as countfractile_60%,
fractile(countsales, 0.8) as countfractile_80%
resident Calculations
group by "FiscalMonth";
NoConcatenate
final_data:
LOAD
Rep as Representaitve,
sumsales,
countsales,
"FiscalMonth",
if(sumsales <= fractile_20%,5,
if(sumsales <= fractile_40%,4,
if(sumsales <= fractile_60%,3,
if(sumsales <= fractile_80%,2,1)))) as FinacialImpactScore,
if(countsales <= countfractile_20%,5,
if(countsales <= countfractile_40%,4,
if(countsales <= countfractile_60%,3,
if(countsales <= countfractile_80%,2,1)))) as ServiceImpactScore,
ApplyMap('MappingTable', "Rep", Null()) as EmpID
//("Sum_Score"*.60+"Count_Score"*.40) as Sales_score
resident Calculations;
drop table Calculations;
Sample Data:
