Not sure I can follow all the calculations but see how this works for your need
Let me know if it helped
>> if [Distinct device count]>1 then avg([Dwell time (min)]) end
1 of 1 people found this helpful
Thanks for a swift reply Jim Dehner ! What you're using is the session dwell time. What I'm looking for instead is the time between each session, so somehow get the timediff between the end of the previous session and the start of the next, but do it separately for each device.. does that make sense?
I didn't forget you but this was tough
I have to admit I was not able to understand your data sheet but I was able to do a simulation using Superstore Data - see attached 10.1 workbook
The issue I was having was determining the last event finish and next event start - so I simulated that using the sales data Order Date and Ship Date
I think that is OK you have separate start and finish dates
I broke the process into steps (you may be able to combine some )
- Calculated the Current Order date >> float(LOOKUP(ATTR([Order Date]),0)) (this is the start of the NEXT event)
- Calculated the last ship date >>>Float(LOOKUP(ATTR([Ship Date]),-1)) (this is the finish of the LAST event)
- Note the Float is need for the next calculation
- Calculated the difference >>if ISNULL([last ship date ]) then 0 Else [current order date]-[last ship date ] END
- Note in my example the date fields are at the day level so the difference is in days as whole numbers -
- your dates will be at the date/time level so the difference will be in days including decimal parts - you can convert to min or what ever you need
- Each of the above 3 is a table calculation that is evaluated at the Order ID level and restarted every customer
I just made a text table to see the calculations - it looks right but you know your data
Does this make sense and can you draw the analogies to your data?
Let me know if this helped
I didn't download your workbook as it's quite large but I have come across this issue before. Check out this post and see if it helps with your problem.
What I did was create 3 LODs: Min Date by Device, Max Date by Device, # of IDs by Device -1(Not counting the first ID). I then created a datediff between min and max dates which I then used as the numerator for the average calculation.