Disitnct command

[delbooth]

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

[petter]

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 , ',' )

[pablolabbe]

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?

[sunny_talwar]

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

[delbooth]

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 ?