Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
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