Forum Discussion

Llewbear's avatar
Llewbear
Icon for Advocate I rankAdvocate I
8 years ago
Solved

Time Spent in each room

Good day,   I have a similar to THIS POST, where I'm also trying to address with ALLEXCEPT(), however mine has a little twist.    I'm trying to answer the following questions:   1 What room is ...
Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    I assumed that travel time is only relevant if done the same day. If you remove the criteria of the same day, then it will work across days as well e.g. night 18/19 of May

    Travel Time =
VAR CurrentRoom = Motion[Room]
VAR CurrentLocation = Motion[Location]
VAR CurrentDate = Motion[Date]
VAR CurrentTimeStamp = Motion[DateTime]
VAR PreviousTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        ALL ( Motion ),
        Motion[DateTime] < CurrentTimeStamp
    )
VAR PreviousRoomTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        FILTER (
            ALL ( Motion ),
            Motion[DateTime] = PreviousTimeStamp
                && Motion[Location] = CurrentLocation
                && Motion[Room] <> CurrentRoom
        )
    )
RETURN
    IF (
        PreviousRoomTimeStamp <> BLANK (),
        CurrentTimeStamp - PreviousRoomTimeStamp
    )
Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
8 years ago

regarding 2) - can you add calculated column for the travel time, and then subtract it from the time in the room formula?

Travel Time = 
VAR CurrentRoom = Motion[Room]
VAR CurrentLocation = Motion[Location]
VAR CurrentDate = Motion[Date]
VAR CurrentTimeStamp = Motion[DateTime]
VAR PreviousTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        ALL ( Motion ),
        Motion[DateTime] < CurrentTimeStamp
    )
VAR PreviousRoomTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        FILTER (
            ALL ( Motion ),
            Motion[Date] = CurrentDate
                && Motion[DateTime] = PreviousTimeStamp
                && Motion[Location] = CurrentLocation
                && Motion[Room] <> CurrentRoom
        )
    )
RETURN
    IF (
        PreviousRoomTimeStamp <> BLANK (),
        CurrentTimeStamp - PreviousRoomTimeStamp
    )



Llewbear's avatar
Llewbear
Icon for Advocate I rankAdvocate I
8 years ago

Good day Stachu, 

 

Thank you for the info. I've tried this formula but it returns no result. I understand the logic you are putting across but it doesn't seem to work.

 

Thanks!

    • Llewbear's avatar
      Llewbear
      Icon for Advocate I rankAdvocate I
      8 years ago

      Hi Stachu, 

       

      This is definately moving in the right direction. So what you have done is calculating the difference in time from the last timestamp in one room vs the first timestamp in the next room (i.e. travel time), which is great, but it does not seem to work across the dates for all occations - not sure why? 

       

      Thanks!!

       

      LlewBear

      • Stachu's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        8 years ago

        I assumed that travel time is only relevant if done the same day. If you remove the criteria of the same day, then it will work across days as well e.g. night 18/19 of May

        Travel Time =
VAR CurrentRoom = Motion[Room]
VAR CurrentLocation = Motion[Location]
VAR CurrentDate = Motion[Date]
VAR CurrentTimeStamp = Motion[DateTime]
VAR PreviousTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        ALL ( Motion ),
        Motion[DateTime] < CurrentTimeStamp
    )
VAR PreviousRoomTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        FILTER (
            ALL ( Motion ),
            Motion[DateTime] = PreviousTimeStamp
                && Motion[Location] = CurrentLocation
                && Motion[Room] <> CurrentRoom
        )
    )
RETURN
    IF (
        PreviousRoomTimeStamp <> BLANK (),
        CurrentTimeStamp - PreviousRoomTimeStamp
    )