5 Replies Latest reply on Aug 17, 2016 4:29 PM by kettan

# Need help calculating time taken for every event

Hi,

The structure of events' data for those using our App is as follows:

Customer IDTimestamp (Epoch Time)Event ID
11000000000E_1
11000000002E_60
21000000005E_2
31000000023E_58
21000000030E_4
11000000035E_4
31000000060E_111

What I want is a calculated field that tells me how much time did every user spend on an event. For example, Customer 1 spent 2 seconds on E_1 because time(E_60)-(E_1)=2 seconds; however time spent by customer 2 on E_2 = time(E_4)-(E_2)=25 seconds. I realize that I will not have time for the last event and in this case, I am comfortable with a null value.

I wish I could say that I have tried something and failed but I am completely new to Tableau and lost . I tried browsing through the forum to find something similar but anything I found relied on using the previous row which doesn't work here because the rows are not necessarily in the right order. The only sanity is the timestamp itself which is in ascending order.

Thanks in advance for helping me

• ###### 1. Re: Need help calculating time taken for every event

Hello Siddharth Nishar,

I had a hard time understanding the issue however I think I finally get it! ... almost.

→ Customer 1

• Event E_1 occurred at 12:20:20
• Event E_60 occurred at 12:20:22

It means the event E_1 lasted for 2 seconds.

Customer 2

• Event E_2 occurred at 12:30:20
• Event E_4 occurred at 12:30:45

It means the event E_2 lasted for 25 seconds.

You would need your [TimeStamp] and a new [EventLength] next to your [EventID]

Here, in the examples, the [EventLength] would be 2s and 25s for E_1 and E_2 respectively.

The calculation should be something more "mathematical" of this:

[EventLength] of [EventID] n+1 = ([TimeStamp] of [EventID] n+1) - ([TimeStamp] of [EventID] n)

From this point, I tried several things and failed.

If someone could help us switch the light ON, that would be vastly appreciated!

1 of 1 people found this helpful
• ###### 2. Re: Need help calculating time taken for every event

Hi Lenaic,

You are indeed correct in your understanding of the problem! Thanks for

giving it a shot!

On Wed, Aug 10, 2016 at 8:24 PM, Lénaïc RIÉDINGER <

• ###### 3. Re: Need help calculating time taken for every event

If I understood your requirement correctly, then you can't solve this with your data how it is atm.

An idea would be to create a timestamp when the customer leaves the app (onStop() in android e.g.).

Then you have your timestamp for the end of the last event and you can calculate the time spent in that event.

• ###### 4. Re: Need help calculating time taken for every event

You could do something like this with a table calculation:

I wonder if there is a LoD (Level of Detail) calculation for the same?

If you would like to have this as a row-level measure rather than a table calculation,

and if your data source is a relational database,

you can do so with a correlated subquery column like this:

```select "Customer ID"
, "Timestamp (Epoch Time)"
, "Event ID"
, ( select min(t2."Timestamp (Epoch Time)") - t1."Timestamp (Epoch Time)"
from "Event data" t2
where t2."Customer ID" = t1."Customer ID"
and t2."Timestamp (Epoch Time)" > t1."Timestamp (Epoch Time)"
) "Duration Seconds"
from "Event data" t1
```

A template for generating underlying data for this SQL Fiddle query is attached below.

We can influence Tableau to feature correlated subqueries by up-voting:

Your question is added to a collection of similar questions in mentioned idea.

I don't remember, but maybe some of the referenced threads have a better or different solution than the one shared above.

Attached workbook version:  9.0

• ###### 5. Re: Need help calculating time taken for every event

An SQL Window Function could be used Instead of a correlated subquery column as shown below:

```select "Customer ID"
, "Timestamp (Epoch Time)"
, "Event ID"
, "Timestamp (Epoch Time)" - lag("Timestamp (Epoch Time)") over ( partition by "Customer ID" order by "Timestamp (Epoch Time)" ) "Duration Seconds"
from "Event data" t1
```

This query and its output (which is the same as earlier, of course) is available here:  SQL Fiddle

It would be better if Tableau had built-in support of SQL Window Functions and therefore posted this idea earlier today:

.