topic Re: MAX Distinct seler per client? in QlikView

MAX Distinct seler per client?

Mon, 24 Feb 2014 06:57:01 GMT

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?

Re: MAX Distinct seler per client?

MK_QSL — Mon, 24 Feb 2014 07:00:57 GMT

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

and

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

Re: MAX Distinct seler per client?

hic — Mon, 24 Feb 2014 07:05:29 GMT

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

Re: MAX Distinct seler per client?

Mon, 24 Feb 2014 07:22:27 GMT

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

Re: Re: MAX Distinct seler per client?

MK_QSL — Mon, 24 Feb 2014 07:32:54 GMT

like this?

Re: MAX Distinct seler per client?

Mon, 24 Feb 2014 07:43:16 GMT

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

Re: MAX Distinct seler per client?

MK_QSL — Mon, 24 Feb 2014 07:54:15 GMT

Dimension Customer

Expressions:

COUNT([transaction nr.])

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

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

Re: MAX Distinct seler per client?

Mon, 24 Feb 2014 08:08:20 GMT

Many thanks it works great

Re: MAX Distinct seler per client?

MK_QSL — Mon, 24 Feb 2014 08:10:12 GMT

Glad that my answer help you to solve your problem.

Re: MAX Distinct seler per client?

oleg_orlov — Mon, 24 Feb 2014 08:14:38 GMT

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

Re: MAX Distinct seler per client?

MK_QSL — Mon, 24 Feb 2014 09:20:57 GMT

Hi,

As Oleg said... FirstSortedValue will not work in case in Two Agent are having maximum sales.

In this case, use below rank instead of FirstSortedValue...

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