Help with grouping with the specified dimention

Wed, 20 Jan 2010 00:45:03 GMT

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.

Help with grouping with the specified dimention

Wed, 20 Jan 2010 19:08:04 GMT


Aggr(Sum(FIELD), DIMENSION)
or
Aggr(distinct Sum(FIELD), DIMENSION)

Help with grouping with the specified dimention

Thu, 11 Feb 2010 00:29:55 GMT

Your reply did got me thinking in the right direction.

What ultimately worked for me was

sum((aggr(count(distinct LOGONDATE), LOGONDATE_MONTH,NTID)))

Thanks!

topic Help with grouping with the specified dimention in QlikView

Help with grouping with the specified dimention

Help with grouping with the specified dimention

Help with grouping with the specified dimention