User login count using set analysis and weekday

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

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.

[#3]

Many Thanks!

I've understood!