| count_distinct problem [message #157922] |
Tue, 07 February 2006 04:17  |
samarsheet
Messages: 7
Registered: November 2005
|
Junior Member |
|
|
Hello
I'm using "count_distinct" function in the discoverer. But if we have a hierarchy it brings only the result of the lowest level while the others are empty.
so can anyone help me please
Samar
|
|
|
|
| Re: count_distinct problem [message #158453 is a reply to message #157922] |
Sat, 11 February 2006 07:03  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Put a sum around it, so: sum(count_distinct (item)). Reason is that there is that on ony higher level in a hierarchy you actually want the grouped result of the count_distinct, not the count itself.
Regards,
Sabine
|
|
|
|
|
|
| Re: count_distinct problem [message #158636 is a reply to message #158545] |
Mon, 13 February 2006 12:41 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Samar,
Sorry about that, I forgot...
Could you please give a more detailed description? I've tried to reproduce your problem with the emp dept tables, but I couldn't.
Regards,
Sabine
|
|
|
|
| Re: count_distinct problem [message #159157 is a reply to message #158636] |
Thu, 16 February 2006 07:39 |
samarsheet
Messages: 7
Registered: November 2005
|
Junior Member |
|
|
Hi,
the problem is:
I have a table that contains calls. each call is linked to an agent. many calls may be linked to the same agent.
So what i'm trying to do is calculate the number of agents. so i've used the following function: "count_distinct(agentid)"
But in the discoverer desktop i'm trying to see the number of agents in a day and in a week in the same sheet. so if the first row was week and the second was day, it only calculates the day while the week is empty.
hope that it's clearer now,
Samar
|
|
|
|
| Re: count_distinct problem [message #159803 is a reply to message #159157] |
Tue, 21 February 2006 13:04 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Samar,
What I did now is:
created "your_table", entries are:
row_id Agend call_desc call_date
1 Jane aaa 1-1-2006
2 Claire aaa 1-1-2006
3 Alice bbb 2-1-2006
4 Bob bbb 1-1-2006
5 Jane aaa 1-1-2006
6 Claire aaa 1-1-2006
7 Jane aaa 1-2-2006
8 Claire aaa 1-2-2006
9 Alice aaa 1-2-2006
10 Jane aaa 1-2-2006
Create new business area with one folder, containing your_table and added a date hierarchy to it. Created a report on this folder, including only date and count_distinct(agent).
Now, the surprise is that the count_distinct works nicely. Both on month/year as on date.
So, I'm actually suspecting the setup of your hierarchy. How did you create it? Is it based on any template hierarchy? Etc....
Regards,
Sabine
|
|
|
|
| Re: count_distinct problem [message #160592 is a reply to message #159803] |
Mon, 27 February 2006 08:25 |
samarsheet
Messages: 7
Registered: November 2005
|
Junior Member |
|
|
Hi Sabine,
thank u for ur reply.But I've retried creating the same table that you've created and by default the date hierarchy was created but still it gave me the same result which is bringing only the last row. even if there was no hierarchy and there are 2 rows Vs a count_distinct column it brings only the last row.
Samar
|
|
|
|
| Re: count_distinct problem [message #163161 is a reply to message #160592] |
Wed, 15 March 2006 04:14 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Samar,
Please try the following: (re)create the folder but without any date hierarchies. Now add a new date hierarchy manually and check the total calculation.
Let me know if this works!
Regards,
Sabine
PS sorry for this late answer.
|
|
|
|
| Re: count_distinct problem [message #163308 is a reply to message #163161] |
Thu, 16 March 2006 02:50 |
samarsheet
Messages: 7
Registered: November 2005
|
Junior Member |
|
|
Hi Sabine,
i didn't try what you asked me to do. But i've tried my function using the discoverer plus and it worked correctly. so it seems the problem is in the discoverer desktop.
so thank u for your help
Samar
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
