Count distinct per Date

[#1]

Hi Guys

This looks like a simple one - but i just cant get around it.

Please see below, i want to able count the distinct number of Agents per Date.

Date	Agent
01-Apr	A
01-Apr	B
02-Apr	A
02-Apr	B
02-Apr	B
03-Apr	A
03-Apr	B
03-Apr	C
03-Apr	A
03-Apr	B
03-Apr	C
03-Apr	A

So the answer should be:

Date	Count of Agents
01-Apr	2
02-Apr	2
03-Apr	3

Further to that, if the user selects a month then it counts the distinct number of Agents for that month.

If the user selects a year then it counts the distinct number of Agents for that year.

Hope this makes sense.

Any help would be appreciated.

Regards,

Magen

[prieper]

If the date/month/year is the dimension, simple formula COUNT(DISTINCT Agent) should do?

Peter

[PrashantSangle]

Hi,

Expression will depends on dimension

if you have date as dimension then by simple

Count(distinct Agent)

will work.

Regards

[robert_mika]

Please see attached

[alexandros17]

this is my example, let me know

[jagan]

Hi,

Simple Count(distinct Agent) works, if this is not your expectation explain bit more.

Regards,

Jagan.

[buzzy996]

try this,

1.Just load ur data as it's.

2. Set Dimesion as Date

3. Set expression as Count(Distinct Agent)

[#8]

Hi guys

This is part of a bigger calculation.

So it something like the sum(Sales)/count (distinct(agents) per date .. if that makes sense

My dimension is not going to be a date, it is City, State etc ..

Regards,

Magen

[jagan]

Hi,

Try like this

Sum(Aggr(count (distinct agents), Date) )

Regards,

Jagan.

[#10]

HI Jagan

That sounds about right but on its own (eg. in a text box) but when i place in my expression then it doesnt calculate for all of my dimensions.

Do i have to place in the dimension as part of my aggr calculation?

Regards,

Magen