Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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..
May be this:
=Count(DISTINCT Left([ID-AREA], 3))
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..
Try below expression
COUNT(DISTINCT RIGHT(ID-AREA,2))
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
Ya that makes sense. Let us know how it goes and close the thread when you are comfortable with your solution.
Best,
Sunny