Hi All,
I need help calculating the duration of time spent in an area by getting the difference in time between an ‘enter’ and ‘exit’ event. The duration needs to be calculated by user id and not the area.
multiple users can enter an area and create an enter/exit event but i need to know how long a user spent in an area.
Below is a sample of the dataset.
many thanks in advance for your help.
This should work:
sumOver(dateDiff(firstValue({timestamp},[{timestamp} ASC],[{id}]),lag({timestamp},[{timestamp} ASC],1,[{user_id},{area_name}]),'SS'),[{user_id}])
Hi Max,
Thanks for your help, could you help walk me through the calculation. I tried to follow the user guide docs for the functions you used but didn’t fully understand.
when i try to use the calculated field i can’t add it as a dimension, the error says i can’t use a custom aggregation and when i try to add it as a measure i get the error message
'Table calculation attributes reference(s) are missing in field wells"
You will need to add timestamp, id, user_id and area name to your visual for it to work.
Thanks Max, i worked that out after i sent you my last message.
its working now, i dropped the sum of as i only want the value for a pair of ‘enter/exit’
is it possible to do the same type of calculation but hide anything that is an action of ‘enter’
when i try to use this calculated field together with something like an ifelse function i get a message about mismatched aggregation
Can you add a filter to your visual to get rid of enter?
If i do, the calculation you provided doesn’t work properly as it needs the enter rows to do the calculation but i just want to hide them not exclude them.
There’s no ability to hide the rows.
What you could try is to use a POST_AGG_FILTER for your calculation and still filter.