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:
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.
Thanks in advance for your help,
Hello @sidhiq91 ,
it worked for this data,
if I change the numbers, it wont work anymore.
check image:
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'.
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,
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 :
filtering on customer1 :
im not sure if there is somehow a way to make this work without filtering.
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.
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
step 2:
add another dimension ( which represents the highest SalesPerson)
formula used : =if(aggr(rank(sum(UnitPrice),1,2),Customer,SalesPerson)<=1,SalesPerson)
step3 :
in the properties panel, uncheck : show null values.
last step :
create the measure in question.
formula used : firstsortedvalue(Product, -aggr(sum(UnitPrice),Customer,SalesPerson,Product))
best regards,
