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

    james.diaz

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

          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

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

            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

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

                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

                  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

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

                    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

                      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

                        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