License

Licensed under a Creative Commons License

Disclaimer

All data and information provided on this site is on an as-is basis and for informational purposes only. We do neither guarantee for accuracy, completeness, suitability or validity of information on this blog nor be liable for any errors in this information or any damages arising from its use. This blog may contain links to other web sites. We do not have any control over the content contained on those sites.

4 posts from May 2015

Sunday, May 31, 2015

Calculated Fields in Tableau for German Public Holidays

The previous post explained why color coding public holidays can be very effective for analyzing daily data, provided the Calculated Fields for all public holidays in the United States and included an interactive calendar for the US on Tableau Public.

The Calculated Fields for the US public holidays are pretty straight forward and not too complicated.

This can be different in other countries. And it sure is in my homeland. In Germany, all variable public holidays depend on Easter Sunday. And, if you read on, you will see that Easter Sunday requires a really complex calculation.

Today’s post provides the Calculated Fields for all German public holidays and a German version of the interactive calendar on Tableau Public.

Saturday, May 30, 2015

Calculated Fields in Tableau for US Public Holidays

For the analysis of daily data, the weekday can be an important and helpful additional information.

For instance: if you are analyzing sales or order figures, incoming calls in a call center, internet usage, web statistics or the like, you will probably see significant differences in the data between working days, weekends and public holidays.

Your views and dashboards should enable the reader to immediately distinguish between the three types, e.g. by color coding. This is pretty easy to do for the weekends, but it takes some additional efforts to include the public holidays, too.

Today’s post explains why color coding daily data by the type of the day can be important for analysis and visualization. The article provides and describes Calculated Fields in Tableau for all US public holidays and put them to use in a Tableau Public dashboard showing an interactive calendar with all public holidays in the US between 1900 and 2099.

Wednesday, May 27, 2015

Interactive Selecting and Highlighting Areas on an Map or an XY Scatter Chart in Microsoft Excel

The previous post demonstrated an alternative technique to implement a direct interaction with an Excel chart using an ActiveX label control sitting on top of the chart. The example used in that post (a geography quiz) allowed a click on a map and evaluated the coordinates of the position the user clicked on.

The technique, however, can also be used in many more cases. If you are working with an XY scatter chart and especially if you are working with a map, interactive selecting and highlighting a user-defined area of the chart can be a very helpful feature for exploring and analyzing the data.

Tableau Software, for one, allows for different ways of selecting and highlighting areas. Microsoft Excel has no such features built in. With the technique described in the previous post and some additional VBA code, however, you can bluff almost the same functionality in Excel.

Today’s article describes how to implement selecting and highlighting features on Excel charts. As always the example workbook and the VBA code is provided for free download.

All of them were taking advantage of the chart object’s mouse event procedures. More precisely, they were based on the great code provided by Jon Peltier here: Get XY on any Chart.

I recently discovered another technique to implement interactivity on Excel charts. Andy Pope uses an ActiveX label control on top of a chart to track and evaluate mouse positions. Unlike the chart object mouse events, Andy’s approach doesn’t require to activate the chart first.

I “stole” Andy’s idea and used his technique to create a little interactive geography quiz in Excel: find European cities on a map. Today’s article describes the implementation of the workbook and the code and – as always - provides the Excel file for free download.