1

I have a chart that shows the number of departures for a given 15 minute interval as seen here.

enter image description here

I need to compound these counts backwards for one hour. For example, the 3 departures shown at 11:00 need to also be represented at the 10:00, 10:15, 10:30, and 10:45 columns. When completed, the 10:00 would have a total of 6 departures (10:15 -> 6, 10:30 ->5, 10:45 -> 4, 11:00 -> 4).

I have done this via VBA in excell, but am now needing to replicate the chart in Tableau and have been beating my head in for about two weeks now. I'd love to hear any and all suggestions.

Michael Byars
  • 117
  • 2
  • 13
  • What have you tried in tableau so far? – tyvich Nov 01 '17 at 16:30
  • I would recommend re-sampling your data before importing it into Tableau. Tableau is very good at visualizing data points you have, but it sounds like you're looking to essentially create new rows, not columns (which can be easily done). You should be able to union on an additional 4 sets of records to represent each additional time that should be displayed. – tyvich Nov 01 '17 at 16:36
  • the problem is that Excel can't handle the amount of rows needed to create this data over the whole schedule. My VBA code creates this per station that is being viewed so the number of rows is irrlevant. I have tried creating the adjusted date/time stamps in the rootdata, but it is still tied to the original date. I wish Tableau would allow for a generic date range that is not built off of rootdata. Then I could actually create what I need. – Michael Byars Nov 01 '17 at 16:41

2 Answers2

1

You can use a Cartesian join against a large enough date range of your choosing to in effect resample your data and add the additional time intervals you desire.

For example, if you have a month's worth of data (min date -> max date = 30 days), then you have (30 * 24 * 4) 2880 15 minute intervals.

  1. Create all those intervals in a separate data sheet
  2. Add a bogus column with value of link for all rows
  3. Create the same bogus in your actual data source
  4. Join the two sheets together on the link column
  5. Create a calculated field that is something along the following:
[Interval] <= [Flight Time] AND [Interval] >= DATEADD('hour',-1,[Flight Time])

This calculated field will evaluate to TRUE when the interval time is within one hour before the flight time. You can then drag this field onto your filter shelf and select TRUE value only. Effectively your [Interval] field becomes your new date field.

I would recommend adding that filter to the context and applying across the entire datasource. Before you add this filter you'll have 2880 times the about of data so be sure to do a live view first. Be careful with extracts using Cartesian joins as you could potentially be extracting more than you bargained for.

See the following links for different techniques on how to do this and re-sampling dates in general in tableau. https://community.tableau.com/thread/151387

Depending on the size of your data (and if a live view is not necessary) it is often times easier and more efficient to do this type of pre-processing outside of tableau in SQL or something like python's pandas library.

tyvich
  • 570
  • 3
  • 13
1

Here is another solution provided from the Tableau Cumunity Forum. I have not tried tyvich's solution yet, but I know this one got me where I needed. Please follow the link to see the solution using moving table calculations.

https://community.tableau.com/thread/251154

Michael Byars
  • 117
  • 2
  • 13