I need help on counting occurrence of a filed in the table grouping by a filed that is NOT in the dimension that I have chosen.
In SQL world the table looks like:
Note a user can log into system any no of time in a data. My query is to plot a graph of unique visitors of the system on a daily basis BUT plot the graph on a monthly basis by summing up the daily unique visitors.
| Logondate | | | ReqID
|
| 12/4/2009 | | | achan |
| 12/3/2009 | | | achan |
| 12/5/2009 | | | alx |
| 12/6/2009 | | | alx |
| 12/1/2009 | | | ateo |
| 12/1/2009 | | | ateo |
| 12/1/2009 | | | ateo |
| 12/2/2009 | | | daheller |
| 12/2/2009 | | | daheller |
| 12/3/2009 | | | jdoe |
| 12/4/2009 | | | mssar |
| 12/5/2009 | | | mssar |
| 12/6/2009 | | | mssar |
My SQL query for plotting the graph is:
select time, sum(count) tot_login from
(select reqid, trunc(logondate, 'month') time, count(distinct logondate) count from trackuserlogin group by reqid, trunc(logondate, 'month'))
group by time
As you can see I have 2 levels of grouping, first to count unique days a visitor has logged into the system in a month and then SUM up this number on a monthly basis for all the users.
I am loading logondate field rounded to month into QV.
So my graph has dimension as login month BUT I am unable to find what to put in the expression field.
I have tried to understand aggr function in vain.
Kindly help or comment or point to the right forum case related to this issue I am facing.
