5 Replies Latest reply on Oct 12, 2016 6:04 AM by Łukasz Majewski

# Indicate if specific Time Period has Lapsed for a recurring Event

I've run into an issue with finding out whether or not a specific minimum amount of time has ever passed for a specific event.

For example, lets say I had a table of numerous Events with the Event in one column, and the Timestamps of those Events in another column.  I want to know how many of those Events had a gap in their timestamps, at any point, that was at least 10 days.

Sample Data Table:

 Event 1 1/01/16 Event 1 1/05/16 Event 1 1/18/16 Event 1 1/20/16 Event 1 1/24/16 Event 2 1/04/16 Event 2 1/05/16 Event 2 1/10/16 Event 2 1/13/16 Event 3 1/01/16 Event 3 1/09/16 Event 3 1/14/16

Ideal Tableau Outcome of Formula:

 Event 1

This is due to Event 1 being the only event that had a difference in time of 10 days or more (from 1/05 - 1/18).

How can this be setup to check all of the DATEDIFF's for every Timestamp that has occurred for every Event?

• ###### 1. Re: Indicate if specific Time Period has Lapsed for a recurring Event

Derek,

Here is my approach.

[EVNET + DATE]  // to avoid auto creation of unnecessary date

[Event Name]+" "+STR([Date])

[days]  //  days from first date of each event

[Date]-({fixed [Event Name]:min([Date])})

[DATE DIFF]

attr([days])-lookup(sum([days]),-1)

1st step

2nd step

3rd step

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Indicate if specific Time Period has Lapsed for a recurring Event

Hi Derek,

Another way of getting the same results is

You can get the date difference of each event using below calc field.

As you've mention difference in time of 10 days or more (from 1/05 - 1/18) so  some modification in the above calculated field

Hope this help.

Mahfooj

• ###### 3. Re: Indicate if specific Time Period has Lapsed for a recurring Event

This appears to be working properly, however it is very slow due to the sheer amount of data that the tables contain, I'm having to do an inner join on two tables to complete this as well.  Even after creating extracts it takes a few minutes simply to update the view.  Are there any ways to optimize either the extracts to only contain the columns I need for the join and/or optimize these calculations?

Secondly, if I want to remove any Events from this equation that only have one occurrence (so there is no DATEDIFF to calculate) where would I put that filter/restriction?

Thanks everyone!

• ###### 4. Re: Indicate if specific Time Period has Lapsed for a recurring Event

One - At extract

Two, After extract

Filtering Date

[Filter]

float({fixed[Event Name] :max([Date])})-float({fixed [Event Name]:min([Date])})

Filter Out Zero at data source filter.

Then Extract.

*** But LOD calc itself consuming the memories and affects the speed, so if you have date list which should be removed, use Date Field directly

Thanks,

Shin

• ###### 5. Re: Indicate if specific Time Period has Lapsed for a recurring Event

Since I do not have your data I cannot tell what seems to be impacting performance most.

But my experience tells me it might be LOD expressions. Attached is a wb with table calculation only.

You may calculate this and pre-filter your data in your data source using sql window functions.