MAX Distinct seler per client?

[#1]

Hi, everyone,

A have a straight table, with customer in dimension and count of distinct agent who had transactions with this customer, how to find who had the most and what is the number of transactions. Any suggestions?

[MK_QSL]

Max(Aggr(Count(Distinct Transaction),Agent))

and

Max(TOTAL Aggr(Count(Distinct Transaction),Agent))

[hic]

What about just sorting the Count(distinct Transactions) descending? Then you will get the interesting agent as the top line in the chart. Very visual and intuitive.

HIC

[#4]

i have these fields:

Customer	transaction nr.	Agent	Value $
john 1	1	A	100
john 2	2	B	125
john 1	3	B	75
john 2	4	B	150
john 1	5	A	200
john 2	6	B	50

i want to get this straight table:

Customer	Distinct Agent	Number of transacions	Max Agent	Max agent value of transactions
	2	6	-	-
john 1	2	2	A	300
john 2	1	3	B	325

[MK_QSL]

like this?

[#6]

Thanks Manish, but could you paste the expresions, because of security issues i can`t download the qvw. Thanks.

[MK_QSL · Accepted solution]

Dimension  Customer

Expressions:

COUNT([transaction nr.])

Max(Aggr(SUM([Value $]),Agent,Customer))

FirstSortedValue(Agent,Aggr(-SUM([Value $]),Customer,Agent))

[#8]

Many thanks it works great

[MK_QSL]

Glad that my answer help you to solve your problem.

[oleg_orlov]

Hi!

But if not only one Agent has the max sum?

You should use someting like that:

Concat(if (Aggr(Rank(Sum(Value)) = 1, Customer, Agent), Agent), ', ')

Regards,

Oleg