Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Daniel29195
Contributor II
Contributor II

Get the highest product sales per the highest salesperson per customer.

Hello All,

I'm new to Qlik sense, and practicing with aggr +  rank functions.

I want to get the highest Product per the Highest Salesperson per Customer.

I tried a lot of formulas, but didn't succeed.

This is the sample I'm practicing on:

ProductData:
load

rowno() as id,*

inline[Customer,Product,UnitSales,UnitPrice,SalesPerson

customer1, product1 , 10 , 5,a
customer1, product1 , 15 , 12,a
customer1, product2 , 4 , 21,a
customer1, product2 , 5 , 14,b
customer1, product2 , 5 , 13,b
customer1, product2 , 5 , 18,b
customer1, product3 , 6 , 12,b
customer1, product3 , 7 , 14,d
customer1, product3 , 15 , 10,a
customer1, product4 , 15 , 22,a
customer1, product4 , 5 , 15,d
customer1, product4 , 4 , 16,c

customer2, product1 , 14 , 16,a
customer2, product1 , 10 , 10,a
customer2, product2 , 5 , 12,b
customer2, product2 , 6 , 22,c
customer2, product3 , 5 , 24,a
customer2, product4 , 4 , 12,b
customer2, product4 , 3 , 23,c
customer2, product5 , 1 , 12,a

customer3, product1 , 15 , 17,b
customer3, product2 , 4 , 14,a
customer3, product2 , 7 , 16,b

customer4, product1 , 9 , 17,b
customer4, product1 , 7 , 18,c
customer4, product1 , 6 , 19,a

];

 

table to create:

Daniel29195_1-1659609102306.png

EXCEPTECTED RESULT:

so example for customer 1 : 

the highest salesperson is 'a '

the highest product per the highest salesperson for customer 1 is product 4,.  so in the above table, i should have product 4. IN THE ROW CORRESPONDING TO customer1.

Daniel29195_2-1659609135937.png

Thanks in advance for your help,

 

 

Labels (2)
5 Replies
sidhiq91
Specialist
Specialist

@Daniel29195  Could you please try the below expression and let me know if it worked.

FirstSortedValue(Product, -Aggr(Max(Aggr(Max(UnitPrice),SalesPerson,Customer,Product)),SalesPerson,Customer,Product))

Daniel29195
Contributor II
Contributor II
Author

Hello @sidhiq91 ,

it worked for this data,

if I change the numbers, it wont work anymore.

check image:

Daniel29195_3-1659614939460.png

 

still salesperson  'a'  is the highest salesperson per customer1,

however , in the below table, it shows the product2, for customer1, (which is related to salesperson 'b' not 'a'.

Daniel29195_1-1659614479778.png

the reason is that you have same row multiple times (check first image),

so you should sum(UnitPrice) first , then get the max,

this is when I couldn't figure it out.

so it like this :

the highest product per the highest salesperson ( for sum(UnitPrice) ) per customer.

thanks in advance,

 

 

Daniel29195
Contributor II
Contributor II
Author

firstsortedvalue(Product,
-aggr
(
{<SalesPerson = {$(=FirstSortedValue(SalesPerson,aggr(rank(Sum(UnitPrice),1,2),Customer,SalesPerson)))}>}
sum(UnitPrice),Customer,Product))

 

this will get the correct values, only when fitlering

 

no filter :

Daniel29195_0-1659623009793.png

 

filtering on customer1 : 

Daniel29195_1-1659623027163.png

 

im not sure if there is somehow a way to make this work without filtering.

 

Daniel29195
Contributor II
Contributor II
Author

firstsortedvalue(Product,
-aggr({<SalesPerson = {$(=FirstSortedValue(SalesPerson,-aggr(Sum(UnitPrice),Customer,SalesPerson)))}>}

sum(UnitPrice),Customer,Product))

 

it turns out, u just needed to remove the rank function, and now it works.

 

Daniel29195_0-1659623787663.png

 

Daniel29195
Contributor II
Contributor II
Author

Update For My Latest Solution,

so it seems ( didn't know about it) , set analysis is evaluated once for a chart and that it will not regard the current dimension value , so the the above solution wont be accurate.

however, I did find a solution that it seems to be 100% accurate, and I'm going to explain it for future reference, or if it could help someone else.

I wanted to get the highest product per highest salesperson per customer.

my table structure to create :  customer , highest product per highest salesperson per customer

to achieve this we should pass by the following steps:

step1 : 

create the table with customer dimension 

Daniel29195_3-1659643617288.png

step 2:

add another dimension ( which represents the highest SalesPerson) 

formula used : =if(aggr(rank(sum(UnitPrice),1,2),Customer,SalesPerson)<=1,SalesPerson)

Daniel29195_4-1659643678468.png

step3 : 

in the properties panel, uncheck : show null values.

Daniel29195_5-1659643721407.png

 

 

last step :

create the measure  in question.

formula used : firstsortedvalue(Product, -aggr(sum(UnitPrice),Customer,SalesPerson,Product))

Daniel29195_6-1659643772916.png

best regards,