This content has been marked as final. Show 2 replies
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.
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.