Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count distinct per Date

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.

 

DateAgent
01-AprA
01-AprB
02-AprA
02-AprB
02-AprB
03-AprA
03-AprB
03-AprC
03-AprA
03-AprB
03-AprC
03-AprA

So the answer should be:

  

DateCount of Agents
01-Apr2
02-Apr2
03-Apr3

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

12 Replies
prieper
Master II
Master II

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
robert_mika
Master III
Master III

Please see attached07-Apr-15 10-16-40 AM.jpg

alexandros17
Partner - Champion III
Partner - Champion III

this is my example, let me know

jagan
Luminary Alumni
Luminary Alumni

Hi,

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

Regards,

Jagan.

buzzy996
Master II
Master II

try this,

1.Just load ur data as it's.

2. Set Dimesion as Date

3. Set expression as Count(Distinct Agent)

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

Hi,

Try like this

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


Regards,

Jagan.

Not applicable
Author

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