# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

## Count distinct with different formats

Hi Everyone,

I want to count the unique customer that have more than 1 # Offered using expression. but some of the CSD. Formatted Number has different format.

(ie. tel:+61399389998;phone-context=national , tel:+61399389998) this should only be counted as 1 customer.

Data:

 CSD.Customer Formatted Number # Offered tel:+61399389998;phone-context=national 39 tel:+61399389998;phone-context=national 23 tel:+61399389998;phone-context=national 2 tel:+61399389998;phone-context=national 1 tel:+61399389998;phone-context=national 0 tel:+61399389998 5 tel:+61399389998 4 tel:+61399389998 1 sip:0anonymous@anonymous.invalid 654 sip:0anonymous@anonymous.invalid 216 sip:0anonymous@anonymous.invalid 77 sip:0anonymous@anonymous.invalid 29 christopher.w@yahoo.com 5

Expected Result:

 Repeat Callers tel:+61399389998 1 sip:0anonymous@anonymous.invalid 1 christopher.w@yahoo.com 1
1 Solution

Accepted Solutions
MVP

## Re: Count distinct with different formats

You might have left a closing parenthesis for the measure.

Also eustisab‌, if possible perform this transformation in the script like this

SubField([CSD.Customer Formatted Number], ';', 1) as RequiredString,

[# Offered]

FROM ...;

Once this is done, you can just use this

Dimension

RequiredString

Expression

Count(DISTINCT RequiredString)

2 Replies
MVP

## Re: Count distinct with different formats

You can define the dimension in the table as:

SubField(CSD,';',1)

And the measure as:

Count(DISTINCT SubField(CSD,';',1)

MVP

## Re: Count distinct with different formats

You might have left a closing parenthesis for the measure.

Also eustisab‌, if possible perform this transformation in the script like this

SubField([CSD.Customer Formatted Number], ';', 1) as RequiredString,

[# Offered]

FROM ...;

Once this is done, you can just use this

Dimension

RequiredString

Expression

Count(DISTINCT RequiredString)