Hi, I have a measure that i call

[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 without put the nº of reception in category and count? I just want to count distinct one measure.



Ricardo Pereira

 

[flex13]

Category count measure would be the easiest way to accomplish this. You could perform a calculation in Impromtu that counts distinct items and add that column to the query.

 

[RicardoPereira]

That doesn't work because if i roll up to the january month i have:
Number of receptions = 13
Number of lines = 13

That's why i need to do a count distinct.
The Impromptu count distinct dont work because if i do that i have duplicated records. If you see i have the same number of reception for 03-01-2003 and 04-01-2003. That count distinct that you suggest will give me:

S2SIT S2DEP Data Reception Line
CP TESTE 03-01-2002 2 12
CP TESTE 04-01-2002 1 1

Number of receptions = 3
Number of lines = 13


What i need when i roll up to the january month is:

Number of receptions = 2
Number of lines = 13

 

[nagrajm]

Ricardo

What you are trying to accomplish in Impromptu with "Count Distinct" is correct except add a new column for "Month" with a calculation Month(Date).

Then;

Reception = Count (Distinct Reception) for Month
Lines = Count (Line) for Month

Hope this helps!!

 

[RicardoPereira]

But i am not using Impromptu.
I user the sql server to transform the data, then i use the architect to connect to transformer.
How do i do that in transformer?