Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Right count of distinct values within chart expression

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

   

IDCompanyPostcodeMarketID-AREASales Rep
100Bob BuildersEXEurope100EXGeorge
100Bob BuildersEXUSA100EXGeorge
101Claires CatsBAEurope101BAGeorge
102Dapper DansBSEurope102BSGeorge
103ElectroBAAsia103BAGeorge
103ElectroBAEurope103BAGeorge
104Bob BuildersEXAsia104EXGeorge

Below are my current results with counts and my required results

   

Current CountReq'd Count
EX:32
BA:32
BS:11
1 Solution

Accepted Solutions
Not applicable

Re: Right count of distinct values within chart expression

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..

5 Replies

Re: Right count of distinct values within chart expression

May be this:

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


Capture.PNG

Not applicable

Re: Right count of distinct values within chart expression

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
Valued Contributor II

Re: Right count of distinct values within chart expression

Try below expression

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

Not applicable

Re: Right count of distinct values within chart expression

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

Re: Right count of distinct values within chart expression

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

Best,

Sunny

Community Browser