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

Help with grouping with the specified dimention

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.

2 Replies
Not applicable
Author


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


Not applicable
Author

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!