Tableau - More on Top N

In my posts of 3 & 5 Nov, I was looking at how to plot monthly graphs for just the top N airports out of many. Originally I achieved this through a complicated method (3/11) creating a combined dimension, but it's rather easier than that.

This is for graphs of main departure airports to a single destination.

1. As before, create a graph by putting month in the column shelf, and one or more measures in the row shelf (eg passengers and flights).
2. Filter by year  (I only want to show a single year's seasonal cycle) and destination airport.
3. Create a calculated field which is just Index() - I named it Rank (which is more or less what it is). Check that it's discrete.
4. Filter on Rank, and from the drop-down select 'edit table calculation'.
5. Choose sort by departing pax descending (so rank 1 is the biggest) - or whatever you want to rank by.
6. In partitioning, select airport of departure.
7. When you click OK you get the description something like: Results are computed along ADEP (sorted descending by Sum of Departing Pax) for each Month of Month. In your case, in place of ADEP (=airport of departure) you will see the dimension for each value of which you want a line on your graph.
8. From the drop-down on rank in the filter pane, select filter and select the top N boxes, however many you want.

So, it still has quite a few steps, but it avoids calculating combo dimensions. 

You can see the results in the 'seasonality at main departure airports' graph in the DepFromAP dashboard. I decided not to make N user selectable, just adds complication for the user.

From EU to you: new flight dashboard

I've added a second dashboard 'DepFromAP' that looks at the Eurostat data. 

For destination airports (intended to be outside the EU, but I think you can still select inside) you can see which are the main EU28 departure points, the nature of the traffic (passengers, flights, load factors, aircraft size) and how these rankings have changed with time.

For example, you can see how in 2014 YTD, both Gatwick and Manchester have overtaken Frankfurt ranked in terms of passenger departures to Dubai.

The new dashboard is in the development and full versions (still with a caveat on 2013 & 2014 data). See links at the top of the page.

I did work out that there's an easier way to pick the top N for the seasonality graph - but I'll explain that in a later post.

Energy dashboard again - more on year to date

Getting to grips with how to partition:

1. Calculating a reference ID for each year such as Div(datediff('week',[BaseDate],[Date]),52) 
2. which can easily be converted to a readable label: STR(9+[YrID])+"/"+Str(10+[YrID])
3. and using first() to pull out the first value in the partition (or more accurately lookup(max([gas]),first()).

That's just what's needed for the energy data, which I choose to start counting from mid September ([BaseDate]), and where they are meter readings, so I need to keep taking differences to get meaningful values.

Learn About Tableau

European flight data dashboard - updated to 2014

The main dashboard is now loaded with all of the data to 2014 - as much as Eurostat currently has.

Check the 'DataAvailability' tab for a rough indication of what's available.

Beware, the 2013-2014 numbers look to be twice the size of the 2012; Eurostat are looking into this as a potential upload problem (probably safe just to halve the values, for a rough estimate - and it means load factors & seats/flight are probably still correct).

I'll freeze APFlows now and work on a different view.

Eurostat - air passenger flow data completeness

Check out the latest version of the full dashboard - now with bump chart to show main destinations varying over time.

To get a sense of the completeness of the avia_par_xx data series that I've loaded from Eurostat, here's a quick chart of the counts of pax from the data that are loaded into the dashboard.

Shows quickly that it's Greece (EL) and France (EF) where delivery seems to have stopped after 2010 - and France missed data in 2004-2006. But other States are pretty complete, even if some joined the process only recently.

The issue with French data seems to be that they reported pax on board and commercial pax flights in 2005, but not departing seats - I'll need to work out how to patch up the gap and check if it's the same issue with the Greek data.

Tableau tech - more on top N within another filter

In investigating bump charts (see development version of dashboard), there may be an easier way to select a subset of destinations for a given departure point than that in the previous post.

This Tableau advice on bump charts (example 4) helped. I used line labels directly rather than their proposed way to label the ends of the lines through other reports (which I couldn't get to work, and in any case could be more difficult if you have a variety of possible start and end years).

I'll come back later to see if I can adapt the main destinations time series chart to use the same method.

Tableau tech - Top N within another filter

Instead of the Top N, which is easy to do in Tableau, I wanted the Top N for the selected departure airport (and even for the year). It took a while to find the right search terms, but the answer is here on the Tableau site. (and on a linked page).

In brief, to get a set of line charts for the top N destination airport (ADES) from a given departure airport (ADEP) you need to do the following:

1. Set up your line chart, with Month in the column shelf say, and one or measures in the row shelf.
2. Filter by departure airport.
3. Select both ADEP and ADES (ctrl-click) and create combined dimension.
4. Drag this combo into the colour field in the measures pane, and sort by descending sum(pax) (or whatever).
5. Create calculated field which is just Index() - I named it Rank (which is more or less what it is).
6. Set it as discrete.
7. Drag to the filter.
8. Right-click and select compute using the combo field.
9. Choose the filter values (eg 1-5).

Obvious! (I hope I recalled all those steps correctly). Here's the final dashboard  - with test data only.

[For links to dashboard - production and development versions - see top of page.]

European airport flows - now complete to 2012

So the APFlows dashboard is now complete, with all the data to 2012 at least. There do seem to be some gaps when monthly data has not been provided by some States (Greece, France for some years). There was a problem earlier with the 2013 data, so I'll come back to this at a future date, after I've linked APComp to a more complete set of data.

There are two versions of the workbook 'Pax' has the full data in - 'Pax Dev' uses only a subset, so if you find only a few airports, you should check.

Here's the Pax. (The seasonality doesn't look as if it's airport of departure specific - I'll check).


[For links to dashboard - production and development versions - see top of page.]

European Airports dashboard - total versus flows

Done some more analysis of the data, and its clear that the thresholds that apply when States send data to Eurostat mean that adding up the flows doesn't give very helpful totals. Which is a shame.

So with the avia_par_xx datasets, focus on showing the flows, like this. Will come back and derive the totals from another dataset.

[For links to dashboard - production and development versions - see top of page.]