Top 20%, Middle 60% and Bottom 20% categorization

[ABP2021]

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%
QQ	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%

[Saravanan_Desingh]

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;

[ABP2021]

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%')
)
)