Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I am trying to determine the number of distinct routes done by vehicles in a week. I gave a table visualisation with vehicler registration as a dimension and a measure column I have used the following command
ColYear = year
Colweek is week number
count( {< ColYear={'2018'},ColWeek = {'28'} >} distinct Route)
Where
ColYear = year
Colweek is week number
The command works for about 90% of the vehicles but a couple don't. I have checked vehicle registration field for issues but they are ok. My question is does the distinct function only evaluate a section of the text. The route field values causing the problem are:
Route
18-28F32SC
18-28T32NOSC
18-28R32SC
18-28M32SC
The command is 1 district route not 4
Thanks
Del
Try this expression in the same place you use the Count function to see if you get a comma-separated list of routes or only one route:
Concat( DISTINCT {< ColYear={'2018'},ColWeek = {'28'} >} Route , ',' )
The distinct clause evaluates all the text in the route field, not only a part of it.
18-28F32SC
18-28T32NOSC
18-28R32SC
18-28M32SC
if for this year and week you have records with all these 4 routes with different names, your result will be 4.
More about usage of distinct clause you can find here Count or Count distinct?
May be you need this
Count({<ColYear={'2018'}, ColWeek = {'28'}>} DISTINCT Left(Route, 5))
By doing this, you will be counting based on the first 5 characters from your string....
Thanks for the suggestions.
I did try :
Concat( DISTINCT {< ColYear={'2018'},ColWeek = {'28'} >} Route , ',' )
and got the following routes for one vehicle as an example
18-29M1 |
18-29R1NO |
18-29T1 |
18-29W1 |
It should be
18-29M1 |
18-29R1NO |
18-29T1 |
18-29W1 |
18-29f1 |
It missed off the 18-29f1, is there an issue with lower case mixes ?