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%

[Aditya_Chitale · Accepted solution]

grouping amount based on customer & Sales Person in the backend solves the issue.

Modify your script as below:

test:
LOAD
num(Amount,'###.00') as Amount,
ID,
SalesPerson,
Customer,
"Year"
FROM [lib://AIF & Offshore BI/Qlik Community.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

grouping:
load
Customer,
SalesPerson,
sum(Amount) as Amount
resident test Group by Customer,SalesPerson;

NoConcatenate

test2:
load
Customer,
SalesPerson,
Amount,
autonumber(SalesPerson,Customer) as Rank
Resident grouping order by Amount desc;

drop table test, grouping;

Output:

Regards,

Aditya

[D_K_Singh]

Excellent, This is what i wanted, however i am getting an error while adding Year. 

Am I doing something wrong?

[Aditya_Chitale · Accepted solution]

table 'test2' is a resident of table 'grouping'. Since 'test2' does not contain Year it is showing the error. You won't be able to add Year in 'test2'

Just remove table 'test'(in your case 'data') from drop statement and rename fields in table 'test2' to avoid synthetic key. Also replace respective fields in table chart in frontend with newly renamed fields.

Note: Doing so will not allow you to add Year in your table chart where you are showing sales,  top 10 & % values. As long as you are fine with it, this solution will work.

Regards,

Aditya

[D_K_Singh]

Thanks Aditya for provide the solution partially. Only thing I am left with display data year wise for which i will have to look for alternative.