Clearly and Simply

Author: Robert

  • Solar Calculations in Tableau

    How to calculate sunrise, sunset, solar noon and daylight hours for any location at any given day in Tableau

     

    Town Clock #2 - Photographer slack12 (flickr.com)In the past few months (on and off), I have been working on a blog post providing 50+ time formulas in Tableau, similar to the article String Calculations in Tableau published in 2014.

    As you can imagine, developing and especially describing 50 Calculated Fields is a lot of work and that’s why this is still in progress. But I am close.

    Anyway, during the development I had the idea of providing Calculated Fields for the time of sunrise and sunset in any location at a selected day. It turned out that this is way more complicated than it may sound and I couldn’t get my head around the math. Luckily, I finally stumbled across a brilliant Excel workbook provided by the Earth System Research Laboratory with all necessary calculations and formulas. Transferring the Excel formulas to Tableau Calculated Fields was only a diligent, but routine piece of work.

    Today’s short post provides a Tableau Public workbook visualizing sunrise, sunset, solar noon and daylight hours for the ~ 8,800 largest cities in the world at any given day. If you need the Calculated Fields for your own workbook, you can download the workbook from the Tableau Public dashboard published below.

    (more…)

  • User-defined Excel Chart Tooltips

    How to provide different options of customized tooltips on a Microsoft Excel chart and let the user decide which one to display

    User Defined Tooltips in Microsoft ExcelOne of the previous posts described four different techniques how to create customizable, meaningful tooltips on XY Scatter Charts in Microsoft Excel:

    Customizable Tooltips on Excel Charts

    Two of the approaches used a camera object (aka linked picture) to allow more formatting options of the tooltip.

    This idea can be taken one step further: with camera objects, you can easily provide more than one type of tooltip and let your user interactively select the tooltip which is most helpful for his analysis.

    Today’s article describes how to enable your user to select from five different tooltips with one single click: dimensions and measures, only a dimension and three additional charts displaying further information on the data point currently hovered over with the mouse. The post describes the idea and the implementation and of course makes the example workbooks available for free download.

    (more…)

  • List of all Files in a Folder and its Subfolders

    How to create a list of all filenames inside a specified folder and its subfolders including additional information like file types, sizes, dates and path lengths in Microsoft Excel

    List of all filenames in a folder and its subfolders - click to enlargeFrom time to time, I am taking the liberty to post something totally off topic (see here or here), i.e. something that has nothing to do with data analysis and data visualization.

    Today’s short article belongs to this category.

    A couple of weeks ago, I ran into a problem with my offline backup software. The application threw a few errors, reporting it could not backup a couple of my documents. The issue was easy to find: the path of those files exceeded the maximum path length of the Windows API (260 characters).

    It wasn’t so easy to fix, though, because the error log file of my backup software isn’t very helpful. I needed something to easily identify the files with a path length exceeding the 260 characters limitation, so I could shorten the folder and file names.

    Hence, I wrote a little tool which automatically creates a list of all files inside a specified folder and all its subfolders, including the file names, the paths, the file types, the dates (created, last modified, last accessed) and the lengths of the path and filename. This list sorted descending by path lengths made it easy to identify the files my backup software couldn’t handle.

    According to this Microsoft article in the Windows Developer Center, the maximum length limitation shall disappear in Windows 10, version 1607. The root cause for creating this workbook and code may go away soon, but I assume the little tool of easily getting a list of filenames inside a specified folder may be helpful on other occasions, too.

    If you are interested, here it is for free download:

    Download retrieve filenames from folder and subfolders (zipped Excel 2010 – 2016 workbook, 34.2K)

    Download, unzip and open the tool, enable macros, click on the import icon at the top of the sheet, select a folder and wait until the code is finished (the status bar at bottom left shows the progress).

    Please be advised that the code is not optimized for performance. Importing the ~41,000 files in my document folder took ~6 minutes on my machine. Not really fast, but since I am not doing this on a daily basis, it is good enough in my book.

    I hope this will be helpful for someone else, too.

    More posts on data analysis and visualization will come soon.

    Stay tuned.

  • Customizable Tooltips on Excel Charts

    How to create customizable, meaningful tooltips on XY Scatter Charts in Microsoft Excel

    Customizable Chart Tooltips on Excel XY Scatter ChartBack in December 2010, I published an article about Better Chart Tooltips with Microsoft Excel. The post described the weaknesses of Microsoft Excel’s standard chart tooltips and provided a VBA-based technique how to get to customizable, meaningful and more useful tooltips in Excel.

    Today, I would like to revisit this topic for several reasons:

    Firstly, Microsoft hasn’t done anything about this shortcoming in the versions 2013 and 2016. The problem of insufficient chart tooltips in Excel remains.

    Secondly, the workbook provided in 2010 implemented the better chart tooltips on another dashboard with additional features, which was published here: Bluffing Tableau Actions with Microsoft Excel. Many people have asked for a simplified, generic template only providing the tooltips on a standard XY Scatter chart.

    Next, reader Will Clark came up with the great idea of using a camera object instead of a simple textbox, in order to have more formatting options.

    Last, but not least, I discovered another approach for creating interactive charts in Excel. I already used and published this in several posts (Another Technique for Interactive Excel Charts, Selecting and Highlighting Areas on Excel Charts, Select Areas on a USA Map in Microsoft Excel and Zooming in and out of Excel Charts), but this technique is also viable for implementing chart tooltips.

    Today’s post will discuss again Excel’s shortcomings in terms of chart tooltips, briefly describe 4 different techniques how to overcome this issue and – as always – provide the Microsoft Excel workbooks for free download.

    (more…)

  • Writing and Reading Tableau Views to and from Databases and Text Files – Part 2

    How to use R, a Tableau connection to R and Calculated Fields to store and load the underlying data of a defined Tableau View in a Database or Text File (part 2 of 2)

    R - Photographer: Karyn Christner (flickr.com)This is the second part of Michael Martin’s guest post on how to use Tableau and R to read and write the underlying data of a Tableau view from / to an ODBC database.

    Michael is the Managing Partner at Business Information Arts, Inc, Tableau Partner, Tableau Certified Consultant, Tableau Instructor and leader of the Toronto Tableau User Group.  Last, but not least, Michael is the only person I know who is using Tableau Software since version 1.0.  

    NOTE: You may need to zoom into some of the screen shots below for legibility reasons. Also, the text below contains references to the “Level of Detail Shelf“.  This is synonymous with the Detail Pill in the Tableau Marks Card within the Tableau 9.x and 10.x user interface.  As mentioned in the introduction to part one of this post, including a ‘$’ character as part of an RODBC object name (for example crs$odbc, crs$odbc.tables, crs$dataset) could cause an error on your system, and the affected calculation will fail.  If this happens, replace instances of $ within an RODBC object name with another character within the affected calculation, such as an underscore – i.e. _.

    Supporting files and a README file are available for download at the end of this part of the post. Please look at the README file first.

    The author would like to thank Leonid Koyfman for his suggestions.

    The first part of this post walked you through the process of

    • downloading and installing R and RStudio
    • installing the required R packages,
    • setting up the ODBC database connection
    • writing the Calculated Fields in Tableau to read the underlying data of a Tableau view from a database

    In the first part, we have only run SELECT queries, but we could also run INSERT, DELETE, UPDATE or INSERT INTO (Append data) queries – all we need to be is pass a valid SQL statement to a Calculated Field that establishes an ODBC connection to the Database and run the SQL statement.

    Today’s second part of the post will

    • show how to insert data of a Tableau view into a database
    • demonstrate the necessity of aligning the orientation of the view and the Table Calculation
    • show how to perform data definition queries
    • provide the general caveats of the technique
    • demonstrate an analysis technique that leverages multiple exports of a Tableau View based on the Superstore Dataset to SQL Server
    • give some insights on performance and upper limits
    • provide some useful links regarding the integration of Tableau and R

    (more…)