Forum Discussion

knmath01's avatar
knmath01
Advocate I
4 years ago

Date Diff based on Multiple Daily Sessions

Hello!

 

I have looked around for the last couple days on here and tried several different calculated columns and measure's but I can't quite get to what I am looking for. I want to calculate the session time per person in a day. A person can have multiple sessions per day and currently there is an id per log in/log it but not per what I would consider a session (log in to log out or log in to expired session). Below is a sample of my data and what I think would be what I would want to then create a date diff off of:

 

Sample Date

USERDATESTATUSLOGIN ID
Bob Smith3/10/2022 8:28:45 AMSUCCESS1239260
Bob Smith3/10/2022 9:15:29 AMSESSION EXPIRED1239350
Bob Smith3/10/2022 9:36:25 AMSUCCESS1239386
Bob Smith3/10/2022 9:49:23 AMSUCCESS1239411
Bob Smith3/10/2022 10:07:31AMSESSION EXPIRED1239453
Kathy Smith3/10/2022 3:25:39 PMSUCCESS1240197
Kathy Smith3/10/2022 4:04:53 PMSESSION EXPIRED1240290
Kathy Smith3/10/2022 4:13:46 PMSUCCESS1240313
Kathy Smith3/10/2022 4:43:55 PMSESSION EXPIRED1240353

 

What I think would work best is:

 

SessionIDUserSucess LogSession Expire
1Bob Smith3/10/2022 8:28:45 AM3/10/2022 9:15:29 AM
2Bob Smith3/10/2022 9:36:25 AMBLANK
3Bob Smith3/10/2022 9:49:23 AM3/10/2022 10:07:31AM
4Kathy Smith3/10/2022 3:25:39 PM3/10/2022 4:04:53 PM
5 Kathy Smith 3/10/2022 4:13:46 PM3/10/2022 4:43:55 PM

 

And I would think off that I could then do something to get how long each session was in hours and minutes? Anything were there is no session expire we essentially ignore when we look at the report now. 

 

I appreciate any advice or help, especially if I am over thinking this. 

 

Thank you!!

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    knmath01 , Create a new column

    new column =
    var _max = minx(filter(Table, [User] = earlier([User]) && [DATE] > earlier([Date])), [Date])
    var _status = minx(filter(Table, [User] = earlier([User]) && [DATE] =_max), [status])
    return
    if(_status = "SESSION EXPIRED", _max, blank())

     

     

    nor you can ignore SESSION EXPIRED rows

    • knmath01's avatar
      knmath01
      Advocate I
      4 years ago

      amitchandak Hi, thank you for the quick response! I created a column using the above and it didn't produce any results, the whole column is blank. Below is what I did for the column:

       

      SessionTime =
      var _max = minx(filter(UserSessions, [USER] = earlier([USER]) && [DATE] > earlier([DATE])), [DATE])
      var _status = minx(filter(UserSessions, [USER] = earlier([USER]) && [DATE].[Date] =_max), [STATUS])
      return
      if(_status = "SESSION EXPIRED", _max, blank())