Right count of distinct values within chart expression

[#1]

Hi, I'm new to Qlikview but am struggling a little on one aspect of a chart.

I have data that includes an ID code, company name and company postcode that I'm trying to sort by a count of postcodes for each sales rep that has been visited.  ID codes are not unique as if more than one market is discussed there is another report with the same ID, therefore a single visit to a customer can have 2 rows for the same ID.

The expression I had tried was COUNT(RIGHT(DISTINCT(ID-AREA,2))) which didn't work, I'm trying to find a solution to get a count of how many visits to each postcode the rep has done.

Any help much appreciated, a simplified version of my data is below

ID	Company	Postcode	Market	ID-AREA	Sales Rep
100	Bob Builders	EX	Europe	100EX	George
100	Bob Builders	EX	USA	100EX	George
101	Claires Cats	BA	Europe	101BA	George
102	Dapper Dans	BS	Europe	102BS	George
103	Electro	BA	Asia	103BA	George
103	Electro	BA	Europe	103BA	George
104	Bob Builders	EX	Asia	104EX	George

Below are my current results with counts and my required results

Current Count		Req'd Count
EX:	3	2
BA:	3	2
BS:	1	1

[sunny_talwar]

May be this:

=Count(DISTINCT Left([ID-AREA], 3))

[#3 · Accepted solution]

Thanks Sunny, ufortunately that doesn't seem to work on the full data set.

What I have done is set the dimension to Postcode and the expression as =COUNT(DISTINCT([ID-AREA]))

Fingers crossed this works..

[perumal_41]

Try below expression

COUNT(DISTINCT RIGHT(ID-AREA,2))

[#5]

That gave me a count of 1 for all. Most reps are only in a few areas so the usual count is more

BA = 5
EX = 10

However these could have had 8 and 20 reports respectively

[sunny_talwar]

Ya that makes sense. Let us know how it goes and close the thread when you are comfortable with your solution.

Best,

Sunny