8 Replies Latest reply on Apr 5, 2017 10:50 AM by Deepak Rai

# Identify users that with consecutive logins exactly 3 calendar days apart

Hello. My data consists of user email dimension, and login datetime.

Is there some way I can put user email dimension into rows, and make some calc or set that will let me filter to keep only the users who had two consecutive logins exactly 3 days apart? E.g., if the user logged in on 1/1/2017 01:53:55, and then again on 1/4/2017 02:32:42, this would be 3 days apart, and would be kept. But if this same user also logged in on 1/2/2017 01:00:01, then they would be excluded, since there is no longer an exact gap of 3 days between logins.

• ###### 1. Re: Identify users that with consecutive logins exactly 3 calendar days apart

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 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

• ###### 2. Re: Identify users that with consecutive logins exactly 3 calendar days apart

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

I've created another calculated field to get the date of third login date using DATEADD()

Finally created a Boolean calculated field to get who has logged in three consecutive days like this

Let me know if you've any query.

Mahfooj

• ###### 3. Re: Identify users that with consecutive logins exactly 3 calendar days apart

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/17-1/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/29-3/30 is 48 hours and 3/30-3/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

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

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)

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]

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

• ###### 6. Re: Identify users that with consecutive logins exactly 3 calendar days apart

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

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

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