7 Replies Latest reply on Nov 12, 2018 9:12 PM by swaroop.gantela

# Advanced table calc and timestamp calc

Hi

I have some table calculations to be done which I usually perform in R but I was wondering if we can do those in tableau directly.

Have attached sample calculations in the attached sheet. But idea is to

1) Subtracting two time stamps in each row for each group

2) Subtracting one time stamp in one row with another time stamp in previous row in another column, for each group

3) Average of outputs from (1) for every ID

4) Average of outputs from (2) for sets of nodes.

LOD and lookup couldn't be used together, so that was one roadblock. And I doubt if part 4 can even be done in tableau.

• ###### 1. Re: Advanced table calc and timestamp calc

Hi Julia,

I've got three of them for you # 1 and # 2 and # 3. I'll think about whether #4 is possible.  Problematic due to the base calculation being a table calc and LOD's cannot use a table calc. Regardless, please see below and attached 10.5 workbook.  Thx, Don

Calculation that looks backwards to previous row:

Partitioned by Deepest and restarting every ID:

And got Dwell Average Time:

2 of 2 people found this helpful
• ###### 2. Re: Advanced table calc and timestamp calc

Hey Don,

Thats awesome! Thank you!

I attached the file with location_id.

• ###### 3. Re: Advanced table calc and timestamp calc

Hi Julia,

I couldn't get anywhere with the last one.  The usual issue of LOD's not supporting use of table calc's...sorry...the Travel Time is a lookup function and it automatically becomes an aggregated calculation and table calc, which can't be used further in an LOD.

Maybe one of the other folks might have an idea...we'll see if they weigh in.  Thx, and glad I was able to help! Don

• ###### 4. Re: Advanced table calc and timestamp calc

Julia,

I'm not sure if this will work for your true setup, but it's an idea.

It uses a self-join which removes the need for lookups.

This left self-join is on [ID1] and a calculated field of

[Seq nbr] +1 on one copy and just [Seq_nbr] on what we'll call the destination copy.

Then the travel time is simply a datediff:

DATEDIFF('second',[Dept Time],[Arr Time (Dest)])/86400

For use in an LOD, I created a string NodePair:

STR([Location ID])+"|"+STR([Location_ID (Dest)])

Then the average nodepair travel time is just:

{ FIXED [NodePair]:AVG([Travel Time Diff])}

Advanced table calc and timestamp calc

1 of 1 people found this helpful
• ###### 5. Re: Advanced table calc and timestamp calc

Great idea Swaroop! Thx!! Don

• ###### 6. Re: Advanced table calc and timestamp calc

Great idea Swaroop! thank you. I am not able to mark your answer as correct since I already marked Don's answer as correct for first three steps. Hope thats fine.

• ###### 7. Re: Advanced table calc and timestamp calc

Julia,

Glad that was helpful, even while it may not be the most efficient.

Perfectly fine, Don's answer is certainly correct, I was just chipping in on the last part.