Set Analysis & Rank - need help

srik00001 — Mon, 16 Oct 2017 22:25:15 GMT

Hello everyone,

Appreciate any help with the below requirement.

I am trying to create a calculation where the first part of the set is in the context of the dimension in the chart while the second needs to exclude the dimension in the chart.

From the below sample file, for each Clerk, I want take the clients and rank all the Customer with a relation to those clients.

For Ex: For Clerk = Adil, Customer2 needs to be ranked against all other customers who have a relationship with Client Sri

For Clerk Steve, Customer 1 needs to be ranked against all other customers who have relationships with Clients Arthur & Sri

My input file is below

Client	Customer	Clerk	Value
Arthur	Customer7	-	2844558675
Arthur	Customer6	-	926561025
Arthur	Customer1	Steve	2844558675
Arthur	Customer3	Matthew Todd Ashworth	2405949750
Arthur	Customer4	John	2405949750
Arthur	Customer5	Stanford	2373053625
Arthur	Customer8	Morgan	2552151375
Arthur	Customer9	Maria	2243299050
Sri	Customer1	Steve	2270423012
Sri	Customer2	Adil	681126420
Sri	Customer3	Brian	1362252840
Sri	Customer5	George	2270423012
Sri	Customer6	James	2043380872
Sri	Customer7	Todd	1589294980
Sri	Customer8	Morgan	2043380872
Sri	Customer9	Maria	1816338732

The Output table in qlikview should be as below

Clerk	count(Client)	sum(Value)	Rank
Adil	1	681126420	8
Brian	1	1362252840	7
George	1	2270423012	1
James	1	2043380872	3
John	1	2405949750	4
Maria	2	4059637782	5
Matthew Todd Ashworth	1	2405949750	4
Morgan	2	4595532247	3
Stanford	1	2373053625	6
Steve	2	5114981687	1
Todd	1	1589294980	6

Thanks,
Sri

topic Set Analysis & Rank - need help in QlikView

Set Analysis & Rank - need help