topic Re: Group by Month in QlikView

Group by Month

Wed, 25 Nov 2020 16:16:04 GMT

I am having data as given below

UserID,LoginDate

user1,07/01/2016

user2, 07/10/2016

user3,08/15/2016

user1, 08/01/2016

user1,09/12/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

Re: Group by Month

el_aprendiz111 — Mon, 31 Oct 2016 20:43:40 GMT

Hi,

Re: Group by Month

MarcoWedel — Mon, 31 Oct 2016 21:54:30 GMT

Hi,

one solution might be:

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