Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Friends,
can any one tell me how to achieve below req-
i have to show HQ wise contribution and there grouping like Top 30%, Middle 50 % and Bottom 20 %, as shown in below table A-
Table A | |||||
HQ | Value | PCPM % | Performer (Top 30 %) | Avg (Middle 50 %) | Non Performer (Bottom 20 %) |
Total | 3,95,375 | 100.00% | |||
HQ1 | 70,255 | 17.77% | 17.77% | - | - |
HQ2 | 39,467 | 9.98% | 9.98% | - | - |
HQ3 | 42,883 | 10.85% | - | 10.85% | - |
HQ4 | 33,975 | 8.59% | - | 8.59% | - |
HQ5 | 40,230 | 10.18% | - | 10.18% | - |
HQ6 | 34,719 | 8.78% | - | 8.78% | - |
HQ7 | 30,210 | 7.64% | - | 7.64% | - |
HQ8 | 27,412 | 6.93% | - | - | - |
HQ9 | 25,164 | 6.36% | - | - | 6.36% |
HQ10 | 13,579 | 3.43% | - | - | 3.43% |
HQ11 | 17,808 | 4.50% | - | - | 4.50% |
HQ12 | 11,423 | 2.89% | - | - | 2.89% |
HQ13 | 5,380 | 1.36% | - | - | 1.36% |
HQ14 | 1,640 | 0.41% | - | - | 0.41% |
HQ15 | 1,230 | 0.31% | - | - | 0.31% |
But whenever i select one HQ . suppose, if here i select HQ1 then the output is like below
Table B | |||||
HQ | Value | PCPM % | Performer | Avg | Non Performer |
Total | 70255 | 100% | 100% | ||
HQ1 | 70,255 | 100.00% | - | - | 100% |
It should be in Performer only but due to the contribution it moved to Non Performer. How to fix it, even after selection it have to be in Performer only and contribution 17.77 %
expression i have used like below
Performer=
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 AGGR(
(($(vAch_MQY_PCPM)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"}, 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 AGGR(
(($(vAch_MQY_PCPM)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"}, I_Date={">=$(vCurrentYearStart)<=$(vCurrentDate)"}, CalendarYear=,T_Month, Period_INL=>} [MR Name]) )
/ $(vPCPM_Month))
,HQ))
,'0.00%')
) )
--------------------------------------------------------------------------
Average=
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 AGGR(
(($(vAch_MQY_PCPM)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"}, I_Date={">=$(vCurrentYearStart)<=$(vCurrentDate)"}, CalendarYear=,T_Month, Period_INL=>} [MR Name]) )
/ $(vPCPM_Month))
,HQ))
,'0.00%')
,0,RowNo()))
)
>=.30 and
(
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 AGGR(
(($(vAch_MQY_PCPM)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"}, I_Date={">=$(vCurrentYearStart)<=$(vCurrentDate)"}, CalendarYear=,T_Month, Period_INL=>} [MR Name]) )
/ $(vPCPM_Month))
,HQ))
,'0.00%')
,0,RowNo()))
)<=0.80)
,
(
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 AGGR(
(($(vAch_MQY_PCPM)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"}, I_Date={">=$(vCurrentYearStart)<=$(vCurrentDate)"}, CalendarYear=,T_Month, Period_INL=>} [MR Name]) )
/ $(vPCPM_Month))
,HQ))
,'0.00%')
)
)
-------------------------------------------------------
Non Performer-
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 AGGR(
(($(vAch_MQY_PCPM)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"}, I_Date={">=$(vCurrentYearStart)<=$(vCurrentDate)"}, CalendarYear=,T_Month, Period_INL=>} [MR Name]) )
/ $(vPCPM_Month))
,HQ))
,'0.00%')
,0,RowNo()))
)
>=0.81,
(
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 AGGR(
(($(vAch_MQY_PCPM)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"}, I_Date={">=$(vCurrentYearStart)<=$(vCurrentDate)"}, CalendarYear=,T_Month, Period_INL=>} [MR Name]) )
/ $(vPCPM_Month))
,HQ))
,'0.00%')
)
)
Thanks in Advance
HI,
Try below one
Performer=
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({1} AGGR(
(($(vAch_MQY_PCPM)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"}, 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({1} AGGR(
(($(vAch_MQY_PCPM)
/ Count(distinct {<[Division Name]={"$(vDivisionSelection)"}, I_Date={">=$(vCurrentYearStart)<=$(vCurrentDate)"}, CalendarYear=,T_Month, Period_INL=>} [MR Name]) )
/ $(vPCPM_Month))
,HQ))
,'0.00%')
) )
Hi Sasikanth , not working..