Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Group by Month

I am having data as given below

UserID,LoginDate

user1,07/01/2016

user1,07/01/2016

user1,07/01/2016

user1,07/01/2016

user1,07/01/2016

user2, 07/10/2016

user3,08/15/2016

user1, 08/01/2016

user1,09/12/2016

user2,09/18/2016

user2,09/18/2016

user2,09/18/2016

user1,10/10/2106


1. I want to display Unique users from this list.

2. I want the report to be group by Month (Example: For July, how many unique users, For Aug, how many unique users, etc)

Please help.

Regards,

Mathan


2 Replies
el_aprendiz111
Valued Contributor

Re: Group by Month

Hi,

FORMAT_MES.png

Re: Group by Month

Hi,

one solution might be:

QlikCommunity_Thread_238254_Pic1.JPG

QlikCommunity_Thread_238254_Pic2.JPG

QlikCommunity_Thread_238254_Pic3.JPG

tabUserAccess:

LOAD UserID,

    Date#(LoginDate,'MM/DD/YYYY') as LoginDate

INLINE [

    UserID, LoginDate

    user1, 07/01/2016

    user1, 07/01/2016

    user1, 07/01/2016

    user1, 07/01/2016

    user1, 07/01/2016

    user2, 07/10/2016

    user3, 08/15/2016

    user1, 08/01/2016

    user1, 09/12/2016

    user2, 09/18/2016

    user2, 09/18/2016

    user2, 09/18/2016

    user1, 10/10/2016

];

tabCalendar:

LOAD *,

    Day(LoginDate) as Day,

    WeekDay(LoginDate) as WeekDay,

    Week(LoginDate) as Week,

    WeekName(LoginDate) as WeekName,

    Month(LoginDate) as Month,

    MonthName(LoginDate) as MonthName,

    Dual('Q'&Ceil(Month(LoginDate)/3),Ceil(Month(LoginDate)/3)) as Quarter,

    QuarterName(LoginDate) as QuarterName,

    Year(LoginDate) as Year,

    WeekYear(LoginDate) as WeekYear;  

LOAD Date(MinDate+IterNo()-1) as LoginDate

While MinDate+IterNo()-1 <= MaxDate;

LOAD Min(LoginDate) as MinDate,

    Max(LoginDate) as MaxDate

Resident tabUserAccess;

hope this helps

regards

Marco