Forum Discussion

Fistachpl's avatar
Fistachpl
Helper III
1 year ago
Solved

Time spent in programm - measure

Hello,

 

 

 

I have a table TProgram with 3 columns:

 

 

 

User, dateandtime, type

 

 

 

User is the name of the user

 

 

 

In this table column dateandtime is the dateand hour operation in format ds.mm.yyyy hh:mm:ss 

 

 

 

Type can be:

 

Login

 

Logout

 

And something other possibilities

 

 

 

Example data:

 

 

 

Adam, 25.01.2025 11:30:00, Login

 

Adam, 25.01.2025 12:00, Logout

 

Karol, 26.01.2025 09:00, Login

 

Karol, 26.01.2025 09:15, Offer made

 

Karol, 26.01.2025 09:30, Logout

 

Karol 26.01.2025 13:00 Login

 

Karol 26.01.2025 13:20, PhoneCall

 

 

 

And Now I want to calculate the time spent in the program on each day. 

 

 

 

The time spent should be calculated by subtracting dateandtime of Logout and Login. Please note that on some days someone can Login and Logout multiple times and therefore I need to have a sum of time spent. Second note is that someone can forget to Logout so the time spent in program should be calculated as last entry on that day (or last before next Login) minus time of Login. 

 

 

 

How can I do that? 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Fistachpl 

    First, create a calculated column for session end time:

    EndTime = 
VAR CurrentUser = TProgram[User]
VAR CurrentDateTime = TProgram[dateandtime]
VAR CurrentDate = DATE(YEAR(CurrentDateTime), MONTH(CurrentDateTime), DAY(CurrentDateTime))
VAR NextLogout = 
    CALCULATE(
        MIN(TProgram[dateandtime]),
        FILTER(
            ALL(TProgram),
            TProgram[User] = CurrentUser &&
            TProgram[type] = "Logout" &&
            TProgram[dateandtime] > CurrentDateTime &&
            DATE(YEAR(TProgram[dateandtime]), MONTH(TProgram[dateandtime]), DAY(TProgram[dateandtime])) = CurrentDate
        )
    )
VAR NextLogin = 
    CALCULATE(
        MIN(TProgram[dateandtime]),
        FILTER(
            ALL(TProgram),
            TProgram[User] = CurrentUser &&
            TProgram[type] = "Login" &&
            TProgram[dateandtime] > CurrentDateTime &&
            DATE(YEAR(TProgram[dateandtime]), MONTH(TProgram[dateandtime]), DAY(TProgram[dateandtime])) = CurrentDate
        )
    )
VAR LastEventOfDay = 
    CALCULATE(
        MAX(TProgram[dateandtime]),
        FILTER(
            ALL(TProgram),
            TProgram[User] = CurrentUser &&
            DATE(YEAR(TProgram[dateandtime]), MONTH(TProgram[dateandtime]), DAY(TProgram[dateandtime])) = CurrentDate
        )
    )
VAR MinNextEvent = 
    SWITCH(
        TRUE(),
        NOT ISBLANK(NextLogout) && NOT ISBLANK(NextLogin), MIN(NextLogout, NextLogin),
        NOT ISBLANK(NextLogout), NextLogout,
        NOT ISBLANK(NextLogin), NextLogin,
        BLANK()
    )
RETURN
IF(
    TProgram[type] = "Login",
    IF(
        NOT ISBLANK(MinNextEvent),
        MinNextEvent,
        LastEventOfDay
    ),
    BLANK()
)

     

     

    Then create a calculated column for session duration:

    Duration (Hours) = 
IF(
    TProgram[type] = "Login",
    DATEDIFF(TProgram[dateandtime], TProgram[EndTime], SECOND) / 3600,
    BLANK()
)

     

    Then create a calculated column to sum the duration:

     

    Total Time Spent (Hours) = 
SUMX(
    FILTER(TProgram, TProgram[type] = "Login"),
    TProgram[Duration (Hours)]
)

     

    Result:

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Fistachpl 

    First, create a calculated column for session end time:

    EndTime = 
VAR CurrentUser = TProgram[User]
VAR CurrentDateTime = TProgram[dateandtime]
VAR CurrentDate = DATE(YEAR(CurrentDateTime), MONTH(CurrentDateTime), DAY(CurrentDateTime))
VAR NextLogout = 
    CALCULATE(
        MIN(TProgram[dateandtime]),
        FILTER(
            ALL(TProgram),
            TProgram[User] = CurrentUser &&
            TProgram[type] = "Logout" &&
            TProgram[dateandtime] > CurrentDateTime &&
            DATE(YEAR(TProgram[dateandtime]), MONTH(TProgram[dateandtime]), DAY(TProgram[dateandtime])) = CurrentDate
        )
    )
VAR NextLogin = 
    CALCULATE(
        MIN(TProgram[dateandtime]),
        FILTER(
            ALL(TProgram),
            TProgram[User] = CurrentUser &&
            TProgram[type] = "Login" &&
            TProgram[dateandtime] > CurrentDateTime &&
            DATE(YEAR(TProgram[dateandtime]), MONTH(TProgram[dateandtime]), DAY(TProgram[dateandtime])) = CurrentDate
        )
    )
VAR LastEventOfDay = 
    CALCULATE(
        MAX(TProgram[dateandtime]),
        FILTER(
            ALL(TProgram),
            TProgram[User] = CurrentUser &&
            DATE(YEAR(TProgram[dateandtime]), MONTH(TProgram[dateandtime]), DAY(TProgram[dateandtime])) = CurrentDate
        )
    )
VAR MinNextEvent = 
    SWITCH(
        TRUE(),
        NOT ISBLANK(NextLogout) && NOT ISBLANK(NextLogin), MIN(NextLogout, NextLogin),
        NOT ISBLANK(NextLogout), NextLogout,
        NOT ISBLANK(NextLogin), NextLogin,
        BLANK()
    )
RETURN
IF(
    TProgram[type] = "Login",
    IF(
        NOT ISBLANK(MinNextEvent),
        MinNextEvent,
        LastEventOfDay
    ),
    BLANK()
)

     

     

    Then create a calculated column for session duration:

    Duration (Hours) = 
IF(
    TProgram[type] = "Login",
    DATEDIFF(TProgram[dateandtime], TProgram[EndTime], SECOND) / 3600,
    BLANK()
)

     

    Then create a calculated column to sum the duration:

     

    Total Time Spent (Hours) = 
SUMX(
    FILTER(TProgram, TProgram[type] = "Login"),
    TProgram[Duration (Hours)]
)

     

    Result:

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Fistachpl's avatar
      Fistachpl
      Helper III
      1 year ago

      Works like a charm. 
      If I can have question. Why did You add columns? Is it not possible to do this via measure?