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
    )
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
    )
    • Llewbear's avatar
      Llewbear
      Icon for Advocate I rankAdvocate I
      8 years ago

      Thanks for this Stachu, this definately guided me in the right direction. 

       

      I've done the following, I've said in one calculated column, where Motion[Room] <> CurrentRoom and another where Motion[Room] = CurrentRoom - then used an IF statement to return the relevant value. I can then just sum the values as required and I have question 1 answered.  Let me know if there is a cleaner way of doing this. 

       

       

      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
    )

       

      Travel Time 2 = 
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
    )

      And finally...

       

      Time Spent =
IF (
    FIRSTDATE ( Motion[Date] ) && ISBLANK ( Motion[Travel Time] )
        && ISBLANK ( Motion[Travel Time 2] ),
    Motion[Time(no secs)],
    IF (
        ISBLANK ( Motion[Travel Time] ),
        Motion[Travel Time 2],
        Motion[Travel Time]
    )
)

      And then the count of motion censor triggers

       

      Activity Count =
CALCULATE (
    COUNT ( Motion[Room] ),
    ALLEXCEPT ( Motion, Motion[Date], Motion[Location], Motion[Room] )
)

      Any revisions will be appreciated, else I am a happy chappie :-)

       

      Thanks again!!

       

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

        maybe I am oversimplyfying, but shouldn't Time Spent be just 

        Time Spent = SUM(Motion[Travel Time])

        it excludes the travel time, and in case you want to add it you can just add the SUM(Motion[Travel Time 2])