topic Re: sum of highest sales contributed agent count in backend in App Development

sum of highest sales contributed agent count in backend

rkpatelqlikview — Mon, 20 Dec 2021 21:08:46 GMT

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.

Re: sum of highest sales contributed agent count in backend

Taoufiq_Zarra — Fri, 24 Jul 2020 10:23:41 GMT

in Script ?

Re: sum of highest sales contributed agent count in backend

rkpatelqlikview — Fri, 24 Jul 2020 11:21:59 GMT

Yes script level.

Re: sum of highest sales contributed agent count in backend

Kushal_Chawda — Fri, 24 Jul 2020 11:55:51 GMT

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)

Re: sum of highest sales contributed agent count in backend

rkpatelqlikview — Fri, 24 Jul 2020 12:16:10 GMT

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

Re: sum of highest sales contributed agent count in backend

rkpatelqlikview — Fri, 24 Jul 2020 13:00:32 GMT

Thanks a lot Kush. Its giving accurate result.

Re: sum of highest sales contributed agent count in backend

Kushal_Chawda — Fri, 24 Jul 2020 13:20:38 GMT

Glad that it worked