
1. Re: Identify users that with consecutive logins exactly 3 calendar days apart
Deepak Rai Mar 31, 2017 7:38 PM (in response to james.diaz)Hi James,
Using a dataset I used to help someone , I could do this . Check the screensheet and the attached in 10.2. I used FIXED LOD fisxed to Individual Name. You can fix it to Email.
Steps are:
 Find MIN Login date per person/email
 Find Max login Date/email
 Find Difference between Minimum and Maximum Dates in Days
 Check if Difference is equal to 3
 Filter out anything not equal to 3 and that's it
Screenshots:
Drag below calculation to Filter:
That' it
Hope it Helps!!!
Thanks
Deepak

Login Dates.twbx 73.9 KB

2. Re: Identify users that with consecutive logins exactly 3 calendar days apart
Mahfooj Khan Mar 31, 2017 10:24 PM (in response to Deepak Rai)Hi Deepak/James
As per the data set which you've used is showing only "Iris Hairston" has logged in three consecutive days.
You approach is right. MAX() login date will not be helpful in this. Sheila hasn't logged in 29 and 30. Correct me if I'm wrong.
James,
Find my approach, lets assume we've data like this
As Deepak has suggested get the First Login date using MIN([Login Date]) in fixed LOD
First Login Date: {FIXED [User Email]:MIN([Login Date])}
I've created another calculated field to get the date of third login date using DATEADD()
Consecutive third time login date: DATE(DATEADD('day',2,[First Login Date]))
Finally created a Boolean calculated field to get who has logged in three consecutive days like this
True/False: {FIXED [User Email]:MAX([Login Date]=[Consecutive third time login date])}
Let me know if you've any query.
Mahfooj

3. Re: Identify users that with consecutive logins exactly 3 calendar days apart
Deepak Rai Mar 31, 2017 11:21 PM (in response to Mahfooj Khan)Hi Mahfooj,
As per requirement from James , he need exact gap of 3 days in between the Dates :
Please have a look at at the Highlighted portion which suggests 1/4/171/1/17= 3 Days. So, as per this requirement, Sheila Lyons would have exact 3 days between logins. Even if you count Hours, then 3/28 to 3/29 is 24 Hours, 3/293/30 is 48 hours and 3/303/31 is 72 hours which makes a gap of 3 Days.
So, in my opinion the calculation is correct. Let me know what do you think. REMEMBER we are not counting the individual dates, but we need the GAP of 3 DAYS between LOGIN Dates. He has clearly mentioned that he needs 3 DAYS APART.
Thanks
Deepak

4. Re: Identify users that with consecutive logins exactly 3 calendar days apart
Okechukwu Ossai Apr 2, 2017 1:44 PM (in response to Deepak Rai)Great approach Deepak.
I downloaded your workbook for personal study. I was wondering what would happen if there are 50 Login Times per user over a long period. MIN(Login Time) may not work because it will consistently fail the 3 days gap condition.
Would the best approach not be to find the DATEDIFF between every 2 consecutive records instead of the MIN and MAX which works only when there are few Login Times per user?
Ossai

5. Re: Identify users that with consecutive logins exactly 3 calendar days apart
Okechukwu Ossai Apr 2, 2017 5:58 PM (in response to james.diaz)Hi Deepak/James,
My alternative solution, assuming large amount of historical login times per user, will be to calculate a continuous difference in date between consecutive login times using the LOOKUP function.
The challenge was how to keep the calculation fixed by Name. Using LOD calculation didn't work, so I fell back on LOOKUP again to achieve that. So the difference in date restarts for every Name with the first record within a Pane calculating as NULL since it doesn't have any prior login time to subtract from.
I also converted the Login Time from DateTime to Date to ensure consistency in DATEDIFF calculation.
NOTE: All table calculations should be set to Compute using "Table Down" when the field is on the rows shelf.
1. Create calculated field [Login Time]
DATE([Incident Created])
2. Create calculated field [DateDiff in days]
IF ATTR([Name]) = LOOKUP(ATTR([Name]), 1)
THEN DATEDIFF('day', LOOKUP(ATTR([Login Time]), 1), ATTR([Login Time])) END
The first line of the above code is used to ensure that DateDiff result is only returned for a specific Name. This mimics partitioning by Name, where the calculation restarts for every Name.
3. Create calculated field [3 Consecutive Dates]
IF [DateDiff in days] = 3 THEN ATTR([Login Time]) END
4. Create calculated field [Date Filter]
[3 Consecutive Dates] = ATTR([Login Time])
5. Create Calculated field [Previous Login Time]
LOOKUP(ATTR([Login Time]), 1)
6. Set up your view as shown below choosing Compute using "Table Down" where necessary.
See attached workbook in version 10.2.
Hope this helps.
Ossai

Login Dates_OO.twbx 55.3 KB


6. Re: Identify users that with consecutive logins exactly 3 calendar days apart
Deepak Rai Apr 2, 2017 8:29 PM (in response to Okechukwu Ossai)Good Work Ossai!! So, James has got a choice to use LOD Expressions to solve this as I did or the way you did it, either way Result is same
Thanks
Deepak

7. Re: Identify users that with consecutive logins exactly 3 calendar days apart
james.diaz Apr 5, 2017 10:36 AM (in response to Okechukwu Ossai)Thanks for the responses, everyone. Wasn't expecting so many this quickly!
I need to do a viz representing the count of these types of users, so it will not be a table. Is there a way to get a list of these users without including all of the other dimensions in the view (e.g., login time)?

8. Re: Identify users that with consecutive logins exactly 3 calendar days apart
Deepak Rai Apr 5, 2017 10:50 AM (in response to james.diaz)Hi James, If you want to see Distinct Count of these Names then as in following Screenshot you would find it 1 as we are counting the person distinctly. See attached.
But if you want to Count the same person, as it has logged Two times between the gap of 3 days, i.e. using His Minimum login and maximum login times , then count is 2 as he logged twice within that gap of 3 days.
Please don't hesitate to mark it Helpful and CORRECT to close discussion.
Thanks
Deepak

Login Dates (1).twbx 76.9 KB
