sum of highest sales contributed agent count in backend

[rkpatelqlikview]

Hi All,

Hope you are doing good.

I need some help on below requirement. Please support.

I have Agents and Sales.I need to show the agent count who contributed 70% from total sales.

Example: In below data My total sales for Jan-2020 month 140M from this 70% means 98M.

Here i want to see count of agents who contributed to 98M. First highest sales 50M,second 40M and third 30M.

It is reaching to more than 98M then my agent count is 3.

How to achieve this? Thanks in advance.

[Taoufiq_Zarra]

in Script ?

[rkpatelqlikview]

Yes script level.

[Kushal_Chawda · Accepted solution]

Assuming that You have data like below

T1:
Load date#(MonthYear,'MMM-YY') as MonthYear,Sales,Agent;
Load * Inline [
MonthYear,Agent,Sales
Jan-20,A1,30
Jan-20,A2,20
Jan-20,A3,50
Jan-20,A4,40
Feb-20,A1,40
Feb-20,A2,100
Feb-20,A3,10
Feb-20,A4,20];

Left Join(T1)
Load MonthYear,
     sum(Sales) as MonthlySales
Resident T1
Group by MonthYear;

T2:
Load *,
    if(MonthYear<>Peek(MonthYear),1,if(peek([%Accum])>=0.7,0,1)) as [Agent_Flag_>70%];
Load MonthYear,
     Sales,
     if(MonthYear<>Peek(MonthYear),Sales/MonthlySales,
     RangeSum(Sales/MonthlySales,peek([%Accum]))) as [%Accum],
     Agent
Resident T1
Order by MonthYear,Sales desc;

Drop Table T1;

You can now show count of the Agent using set analysis as below

Count({<[Agent_Flag_>70%]={1}>}Agent)

[rkpatelqlikview]

Thanks Kush, Am just trying same way. I will check and update. Thanks again. 

[rkpatelqlikview]

Thanks a lot Kush. Its giving accurate result.

[Kushal_Chawda]

Glad that it worked