How to do a count distinct ?

[RicardoPereira]

Hi,

I have a measure that i call "number of receptions" that must count the number of receptions.
How do i do that?

See an example of my data:

S2SIT S2DEP Data Reception Line
CP TESTE 03-01-2002 51 1
CP TESTE 03-01-2002 52 1
CP TESTE 03-01-2002 52 2
CP TESTE 03-01-2002 52 3
CP TESTE 03-01-2002 52 4
CP TESTE 03-01-2002 52 5
CP TESTE 03-01-2002 52 6
CP TESTE 03-01-2002 52 7
CP TESTE 03-01-2002 52 8
CP TESTE 03-01-2002 52 9
CP TESTE 03-01-2002 52 10
CP TESTE 03-01-2002 52 11
CP TESTE 04-01-2002 52 12

I should have for the moth 1
Number of receptions = 2
Number of lines = 13


How do i do that?

 

[griffindm]

Ricardo,

Group on month, do a count distinct on the unique identifier key (if there is one, combine multiple columns to create such a key if not) for the table, and do simple count on the same column. This should give you what you want.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[RicardoPereira]

In architect how do i made a count distinct ?
Could you please explain to me.

Thanks in advance

 

[ilkley]

Hi Ricardo

If you are attempting this in Transformer, you could create a measure of type Category Count based over the Reception dimension. Also, have a column that just contains the value of 1 on all rows to rollup as a Column type measure that will give you the actual number of lines. This should give:

Receptions = 2
Lines = 13

Regards

Paul

 

[RicardoPereira]

hi Paul,

I dont want to have the Reception as a dimension because i have more than 100.000 records. One dimension with more than 100.000 records is too much.