BusinessObjects Board

Distinct Count measure in IDT 4.1

building semantic layer
#1

Trying to create a simple measure, new field in a Universe. A field that will do a distinct count based on an Account Number. An Acct Number might repeat several times in a table, but I only want the distinct count for that acct number. When I do Count(account number), i get the total lines for that account number, which could be up to 20. I just want to see 1. How do I create this distinct count measure?

davidbehr (BOB member since 2010-12-22)

#2

I’ll answer my own question:

Count(distinct AccountNumber)

davidbehr (BOB member since 2010-12-22)

#3

BTW: In this case, aggregation should be put to “Database delegated”.

KFonMurphi :fr: (BOB member since 2007-10-16)

#4

Actually count function will return distinct values. then why again using distinct in count. “Count” will return distinct values and “count all” will return all the values means it will contains duplicate values.

surya_sudheer :india: (BOB member since 2006-11-01)

#5

Hi,

You are talking about syntax in WebI, it means on the report side. The OP talked about SQL syntax, it means on the database/universe side.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#6

Or the aggregation projection should be set to none in the objects properties.

https://bobj-board.org/t/107995/6

Mak 1 :uk: (BOB member since 2005-01-06)