User login count using set analysis and weekday

Fri, 11 Oct 2013 07:52:25 GMT

Hello,

I'm new to Qlikview.

I would appreciate any help with the following issue.

I have one table for User login information like as below:

LoginTime UserID

2013-10-10 00:00:00 UserA

2013-10-10 11:11:11 User A

2013-10-11 22:22:22 User B

・・・

I want to analyze User activity per weekday.

So I want to count Unique User count who login one weekday and login several times within one week group by login days count in that week , from 1 to 7.

My output image is :

For Sunday analysis

LoginWeek 1Login 2Login 3Login 4Login 5Login 6Login 7Login

2013-9-22 11 22 33 44 55 55 77

2013-9-29 13 23 35 47 52 35 57

・・・・

When I login Sunday 9/22 and login 4 times within that week, then I'm in 2013-9-22, 4Login group.

I made 1 dimension:

Week(LoginTime).

And 7 expressions:

one is :

count(distinct {$

<Weekday(LoginTime) = {'Sunday'},

UserID= {"=count(distinct (UserID& Day(LoginTime)) ) = 1"} >

}

UserID)

this is for 1login count within one week.

I changed '1' to 2,3,4,5,6,7 and made 7 expressions.

When I select only one week in LoginTime calendar object, the result looks fine.

(attached 1week.png )

But when I select 2 weeks, I have a problem.

(attached 2week.png)

It calculates all login days count within 2 weeks per User who logins each Sunday.

(If I made expression for 8 logins, 9 logins, Some Users are counted.)

I want to limit login days count in each one week.

Thanks in advance,

Tomoaki.

Re: User login count using set analysis and weekday

Gysbert_Wassenaar — Fri, 11 Oct 2013 09:11:02 GMT

It may be easier to use a calculated dimension or you can precalculate the number logins per user per day in the script. I've created an AsOfWeek table in the script to link every day with the next 7 days. See attached example. If your definition of 'same week' is based instead on the week of the year the day falls in you can use the WeekStart and Day fields instead of Week and WeekDay.

Re: User login count using set analysis and weekday

Tue, 05 Nov 2013 09:09:25 GMT

Many Thanks!

I've understood!

topic User login count using set analysis and weekday in QlikView

User login count using set analysis and weekday

Re: User login count using set analysis and weekday

Re: User login count using set analysis and weekday