How to get total sales amount by sales person and cust. And also get sum of top 10 (sales amount) by sales person in for each customer

[D_K_Singh]

Hi everyone,

I am trying to show  sales amount of total by customer and also sum of top 10 sales person's sales amount. then calculating the percentage like-top 10 sales amt/Total sales amt.

I need to calculate all the 3 measures.

I have sales person as well in dataset but do not want to show in table.

I used rank function but failed to do that.

Please provide your help. sample of data are given below.

Customer	Total Sales amt by sales person	top 10 sales amt by sale person	top 10 sales amt/Total sales amt
cust1	1,000.0	800.0	80.0%
cust2	800.0	500.0	62.5%
cust3	600.0	300.0	50.0%
cust4	700.0	100.0	14.3%
cust5	200.0	180.0	90.0%
cust6	500.0	400.0	80.0%
cust6	100.0	60.0	60.0%
Grand Total	3,900.0	2,340.0	60.0%

[Nosrac]

Sum(Aggr((YourCalculationField)),Customer)

[D_K_Singh]

Thanks for your response. This will give only the total sales by customer, I need total sales by customer in a column and also need top 10 sales amount by sales_person for each customer as shown in the table. I have sales person in data set. I need calculation of both the column.

I need output like below.

Customer	Total Sales amt by sales person	top 10 sales amt by sale person
cust1	1,000.0	800.0
cust2	800.0	500.0
cust3	600.0	300.0
cust4	700.0	100.0
cust5	200.0	180.0
cust6	500.0	400.0
cust6	100.0	60.0
Grand Total	3,900.0	2,340.0

[Nosrac]

From what i understand you would like to generate the third column % value based on the measure value of "total sales amt by sales person" / "top 10 sales amt by sale person" but no in table form and by customer.

if this is done in data load editor then:

Total sales amt by sales person / top 10 sales amt by sale person as "Third field name"

if this is done in chart KPI or whichever chart that are not table:

Sum(Aggr(top 10 sales amt by sale person/Total Sales amt by sales person),Customer)

then format by number -> 0.0%

[D_K_Singh]

Dear, I need to calculate all the 3 columns. If i get top 10 then not able to get total sales, if get total sales then not able to calculate top 10.

[Aditya_Chitale]

@D_K_Singh ,

from what I understood by your post is you want to divide sum of top 10 salesperson grouped by customer with  total sum of salespersons grouped by customer and show % values.

to achieve that, firstly rank the salespersons in backend itself using autonumber function.

autonumber(salesperson,customer) as Rank

then using below expression, you can find customer wise top 10 salesperson total.

sum({<Rank={">=1<=10"}>}amount)

finally divide this by total amount to calculate % values.

Sample data used:

test:
load * Inline
[
customer,salesperson,amount
cust1,SP1,100
cust1,SP2,200
cust1,SP3,300
cust1,SP4,400
cust1,SP5,500
cust1,SP6,600
cust1,SP7,700
cust1,SP8,800
cust1,SP9,900
cust1,SP10,1000
cust1,SP11,1100
cust1,SP12,1200
cust1,SP13,1300

cust2,SP1,10
cust2,SP2,24
cust2,SP3,37
cust2,SP4,40
cust2,SP5,50
cust2,SP6,60
cust2,SP7,79
cust2,SP8,81
cust2,SP9,94
cust2,SP10,10
cust2,SP11,110
cust2,SP12,120
cust2,SP13,130

cust3,SP1,18
cust3,SP2,200
cust3,SP3,15
cust3,SP4,1
cust3,SP5,5
cust3,SP6,1100
cust3,SP7,57
cust3,SP8,45
cust3,SP9,79
cust3,SP10,21
cust3,SP11,211
cust3,SP12,212
cust3,SP13,213

cust4,SP1,100
cust4,SP2,200
cust4,SP3,300
cust4,SP4,400
cust4,SP5,500
cust4,SP6,600
cust4,SP7,700
cust4,SP8,800
cust4,SP9,900
cust4,SP10,1000
cust4,SP11,1100
cust4,SP12,1200
cust4,SP13,1300

];

NoConcatenate

test2:
load 
    customer,
    salesperson,
    amount,
    autonumber(salesperson,customer) as Rank
Resident test order by amount desc;

drop table test;

Output:

Hope it helped !

Regards,

Aditya

[Aditya_Chitale]

@D_K_Singh  you will need to rank the sales in backend itself. if you are looking for a frontend approach using rank() function, you will have to add salesperson field in your chart as that field will determine the sales ranking.

If this solution helped, kindly mark it as solution.

Regards,

Aditya

[D_K_Singh]

@Aditya_Chitale Still I am struggling with the issue as this is not giving me correct ranking due to which it is showing incorrect sum amount. below is the extract of table . I would like to rephrase once again.

suppose I have 10 customer and each customer has 100(dynamic) sales person. So I am trying to calculate total sales of each customer as well as total sales of top 10 sales person(by amount) for each customer.  We are on right path.

Note: Also I have Year column in data set. 

test:
load
[cost center name],
[request by],
[invoice paid amount],

autonumber([request by],[cost center name]) as Rank
Resident Sheet1 order by [invoice paid amount] desc;

drop table Sheet1;

customer	salesperson(request by)	Sum(amount)	Rank
A	AB	414442895.8	1
A	BC	25763685.24	2
A	CD	20963611.15	3
A	DE	4996919.58	4
A	EF	16544770.32	5
A	GH	6314630.88	6
A	ABC	7370194.73	7
A	DEF	8100878.37	8
A	GHI	8277034.58	9
A	JKL	1979224.74	10
A	IJK	4035028	11
A	JKL	14078672.28	12
A	KLM	1700000	13
A	MNO	4202678.37	14
A	NOP	3027540.43	15

[Aditya_Chitale]

Doesn't matter if your data has year or not.  We are ranking sales based on group of customer & sales person. so even if a salesperson has two different year entries, the rank for both entries will be same.

If possible can you share sample data having similar structure of your original data ? You can jumble the numbers.

Regards,

Aditya

[D_K_Singh]

I have attached the sample data for your reference.