Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Friends,
How to categorize the Users base on percentage as Top 20 % , Middle 60 % and Bottom 20%.
Data
Name | Percentage | |
A | 5.83% | Top 20% |
B | 5.60% | Top 20% |
C | 3.59% | Top 20% |
Z | 3.51% | Top 20% |
E | 3.35% | Middle 60% |
F | 3.35% | Middle 60% |
G | 3.35% | Middle 60% |
H | 3.35% | Middle 60% |
N | 3.35% | Middle 60% |
O | 3.35% | Middle 60% |
K | 3.35% | Middle 60% |
L | 2.89% | Middle 60% |
M | 2.65% | Middle 60% |
Q | 2.62% | Middle 60% |
R | 2.62% | Middle 60% |
S | 2.23% | Middle 60% |
AA | 2.23% | Middle 60% |
SS | 2.15% | Middle 60% |
DD | 2.00% | Middle 60% |
FF | 1.89% | Middle 60% |
G | 1.89% | Middle 60% |
GG | 1.82% | Middle 60% |
HH | 1.35% | Middle 60% |
Q | 1.31% | Middle 60% |
1.31% | Middle 60% | |
WW | 1.31% | Middle 60% |
EE | 1.31% | Middle 60% |
RR1 | 1.31% | Middle 60% |
RR2 | 1.18% | Middle 60% |
RR3 | 1.18% | Middle 60% |
RR4 | 1.06% | Middle 60% |
RR5 | 1.03% | Bottom 20% |
RR6 | 0.93% | Bottom 20% |
RR7 | 0.85% | Bottom 20% |
RR8 | 0.84% | Bottom 20% |
RR9 | 0.82% | Bottom 20% |
RR10 | 0.77% | Bottom 20% |
RR11 | 0.75% | Bottom 20% |
RR12 | 0.74% | Bottom 20% |
RR13 | 0.74% | Bottom 20% |
RR14 | 0.71% | Bottom 20% |
RR15 | 0.71% | Bottom 20% |
RR16 | 0.71% | Bottom 20% |
RR17 | 0.71% | Bottom 20% |
RR18 | 0.71% | Bottom 20% |
RR19 | 0.71% | Bottom 20% |
RR20 | 0.57% | Bottom 20% |
RR21 | 0.54% | Bottom 20% |
RR22 | 0.54% | Bottom 20% |
RR23 | 0.53% | Bottom 20% |
RR24 | 0.52% | Bottom 20% |
RR25 | 0.50% | Bottom 20% |
RR26 | 0.50% | Bottom 20% |
RR27 | 0.50% | Bottom 20% |
RR28 | 0.50% | Bottom 20% |
RR29 | 0.47% | Bottom 20% |
RR30 | 0.47% | Bottom 20% |
RR31 | 0.47% | Bottom 20% |
RR32 | 0.46% | Bottom 20% |
RR33 | 0.44% | Bottom 20% |
RR34 | 0.42% | Bottom 20% |
RR35 | 0.40% | Bottom 20% |
RR36 | 0.39% | Bottom 20% |
RR37 | 0.37% | Bottom 20% |
RR38 | 0.33% | Bottom 20% |
RR39 | 0.27% | Bottom 20% |
RR40 | 0.26% | Bottom 20% |
RR41 | 0.23% | Bottom 20% |
RR42 | 0.18% | Bottom 20% |
RR43 | 0.09% | Bottom 20% |
RR44 | 0.00% | Bottom 20% |
RR45 | 0.00% | Bottom 20% |
Try this,
tab1:
LOAD * INLINE [
Name, Percentage
A, 5.83%
B, 5.60%
C, 3.59%
Z, 3.51%
E, 3.35%
F, 3.35%
G, 3.35%
H, 3.35%
N, 3.35%
O, 3.35%
K, 3.35%
L, 2.89%
M, 2.65%
Q, 2.62%
R, 2.62%
S, 2.23%
AA, 2.23%
SS, 2.15%
DD, 2.00%
FF, 1.89%
G, 1.89%
GG, 1.82%
HH, 1.35%
Q, 1.31%
QQ, 1.31%
WW, 1.31%
EE, 1.31%
RR1, 1.31%
RR2, 1.18%
RR3, 1.18%
RR4, 1.06%
RR5, 1.03%
RR6, 0.93%
RR7, 0.85%
RR8, 0.84%
RR9, 0.82%
RR10, 0.77%
RR11, 0.75%
RR12, 0.74%
RR13, 0.74%
RR14, 0.71%
RR15, 0.71%
RR16, 0.71%
RR17, 0.71%
RR18, 0.71%
RR19, 0.71%
RR20, 0.57%
RR21, 0.54%
RR22, 0.54%
RR23, 0.53%
RR24, 0.52%
RR25, 0.50%
RR26, 0.50%
RR27, 0.50%
RR28, 0.50%
RR29, 0.47%
RR30, 0.47%
RR31, 0.47%
RR32, 0.46%
RR33, 0.44%
RR34, 0.42%
RR35, 0.40%
RR36, 0.39%
RR37, 0.37%
RR38, 0.33%
RR39, 0.27%
RR40, 0.26%
RR41, 0.23%
RR42, 0.18%
RR43, 0.09%
RR44, 0.00%
RR45, 0.00%
];
tab2:
NoConcatenate
LOAD RowNo() As ID,*
Resident tab1
Order By Percentage Desc;
Left Join(tab2)
LOAD Count(Percentage) As Tot
Resident tab2;
Left Join(tab2)
LOAD *, If(ID/Tot<=.2,'Top 20%',
If(ID/Tot>.2 And ID/Tot<0.8,'Middle 60%',
'Bottom 20%'
)
) As Output
Resident tab2;
Drop Table tab1;
Hi Saran, i want to doc this in front end, i have tried with below expression its working but when i am selecting any HQ (Name) its group also changes, please suggest how can i bypass that particular field(HQ). below is my expression for Top 30 %
=if(
(
rangesum(above(
Num(
Sum(Aggr(
(($(vAch_MQY_PCPM)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"}, I_Date={">=$(vCurrentYearStart)<=$(vCurrentDate)"}, CalendarYear=,T_Month, Period_INL=>} [MR Name]) )
/ $(vPCPM_Month))
,HQ))
/
Sum(total {<HQ=>}
Aggr(
((
$(vtest)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"},HQ=, I_Date={">=$(vCurrentYearStart)<=$(vCurrentDate)"}, CalendarYear=,T_Month, Period_INL=>} [MR Name])
)
/ $(vPCPM_Month)),
HQ))
,'0.00%')
,0,RowNo()))
)
<=.30,
(
Num(
Sum(Aggr(
(($(vAch_MQY_PCPM)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"}, I_Date={">=$(vCurrentYearStart)<=$(vCurrentDate)"}, CalendarYear=,T_Month, Period_INL=>} [MR Name]) )
/ $(vPCPM_Month))
,HQ))
/
Sum(total {<HQ=>}
Aggr(
((
$(vtest)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"},HQ=, I_Date={">=$(vCurrentYearStart)<=$(vCurrentDate)"}, CalendarYear=,T_Month, Period_INL=>} [MR Name])
)
/ $(vPCPM_Month)),
HQ))
,'0.00%')
)
)