2 Replies Latest reply on Aug 10, 2016 7:27 PM by jenn.ng

# How to get difference across rows?

I have a data set with info on when someone starts and stops an activity.

I want to find the average time someone spends on each activity.

the time variable is a unix timestamp so the difference will be time_event_close - time_event_open

This is my sample dataset:

"person_id","item_id","time","event","type","date"

1,255,1379312026,"open","A",2013-09-16 02:13:46

1,255,1379312086,"close","A",2013-09-16 02:14:46

1,182,1379312926,"open","A",2013-09-16 02:28:46

1,182,1379313046,"close","A",2013-09-16 02:30:46

1,81,1379314006,"open","A",2013-09-16 02:46:46

This is what i have on Tableau:

i just need to take:

Average(unix timestamp of close - unix timestamp of open) for each item_id.

If that is not possible, i could use date(hour) such that the open and close unix timestamps are side by side:

However im not sure how i can get the difference still.

Tried creating a calculated filed called delta and putting it on row shelf but didnt work.

Work book attached.

• ###### 1. Re: How to get difference across rows?

Hi, Jenn

I don't know how to convert your Unix time to standard time format, but here is calculation results before conversion.

One more thing, there are couple of items which has two start time, or no close time.

I assumed that if there are multiple start time, the start time should be smallest time.

Here is my approach then.

Calculated filed with LOD.  //Overview: Level of Detail Expressions

[Time Open 2]

{fixed [Item Id]:min([time open])}

[Time Close 2]

{fixed [Item Id]:min([time close])}

[Delta Time]  // there is only one value, so either one of  min, max, avg works.

min([Time Close 2])-min([Time Open 2])

Thanks,

Shin

9.2 attached.

• ###### 2. Re: How to get difference across rows?

if i replace person_id and item_id with Type, how can i get the average(delta time) by type by day of week?

Because it is set to min(time open), values will not show when i have more data for other days.

Workbook with changes attached.