1 of 1 people found this helpful
Hello Siddharth Nishar,
I had a hard time understanding the issue however I think I finally get it! ... almost.
Let's add some more concrete values with your examples:
→ 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!
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 <
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.
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
See more about this particular query in SQL Fiddle.
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
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: