viztips

[viztips]

Generating Dummy Data Sets

Creating dummy data is often a big pain; making variations and randomizing the values can become a pretty manual process.

Microsoft Excel can make the process far less painful, specifically using the rand() and randbetween() functions.

The =Randbetween([start num], [end num]) formula can be used to generate random whole numbers. If you are seeking to generate fake transaction amounts, which are decimal numbers, simply specify your range in the rand between function, multiply by the rand function (generates a number between 0 and 1) and then round off to the correct number of digits.

For instance, generating dollar amounts you can use the following formula:

=round(rand() * randbetween([min num],[max num]) ,2) 

Dates can also be generated as

=randbetween(date([stardate]), date([enddate])) 

For non numeric values, instances where you would like to randomize country codes or customer types, simply create a separate lookup table with unique values for your data set, and add an index column with the values numbering each row. Next, use the randbetween function inside a vlookup.

=vlookup(randbetween(1,[endnum]), [lookupref], [colref]) 

At the end you can copy all the cells and then paste as values to remove the formulas.

Happy data generating!

[viztips]

Visual Flow - Always Direct the Eye

It's always ALWAYS always important to remember when creating dashboards that order and placement of visuals matters. In order for dashboards to make sense, they must be grouped together, and they must have a clear path for the reader. Naming visuals with number headers can sometimes help, (ex: 1.1 Sales internationally 1.2 Sales in the US 1.3 Sales by state) but its also important to remember how the audience reads; generally left to right and/or up to down. 

Below are a few examples to illustrate my point: 

Let's start with a visual that has the impossibly complex job of tracing connections in Game of Thrones. It is a very valiant attempt to make order of the madness that is the Game of Thrones, and there is a general sense of trying to read from top to bottom, but the read is not particularly intuitive. 

by HeatherJones.  Explore more visuals like this one on the web's largest information design community - Visually.

Now let's stray far in the opposite direction. This simple visual has a clear path; top to bottom. The reader has no trouble in determining the message. Many will prefer this effective approach; I would use this for a client that is only concerned with high level details.

Explore more visuals like this one on the web's largest information design community - Visually.

Perhaps however the dashboard needs to have more or deeper content, such as for an analyst looking for the details. Here is a middle ground I tend to stray in to. Visuals are grouped together and sectioned off around a singular message per section. Everything is related, but maybe the exact order of what I am reading isn't defined. 

  by infogr8. Explore more visuals like this one on the web's largest information design community - Visually.

Of the examples, an analyst should aim for the second option straying into the 3rd as needed.

[viztips]

Aligning Objects in Powerpoint -- Just Gantt get Enough

A slight deviation from the other tools and softwares written about here, however Powerpoint was and is a data presentation tool that is will be used in Analytics, if only as business accompaniment. 

Aligning objects in powerpoint can be a royal pain. Especially for Gantt Charts, example from http://www.officetimeline.com/ below.

However, there is a shortcut that I found only after years and years of using Powerpoint.

First, Create a bunch of objects like boxes, textboxes, etc. Align them generally where you want them to be, but dont stress about perfect alignment. 

Second, select a group of objects that you want to align to be level with one another, either vertically or horizontally. In the Drawing tools, format bar, there is option to align top, right, left,bottom, or center. There's even an option to distribute horizontally or vertically if more than 2 objects are selected, with the capability to evenly space out all objects so long as the upper (left) and lower (right) most objects are spaced correctly. 

Enjoy!

[viztips]

Alias a Value in Tableau

In Tableau it is very easy to change a value in the data. For instance let’s say I have the value “IloveTableau” and I want it to be “I love Tableau”, and in general the other values in the column are all fine as is.

When that field is active on a worksheet, meaning that it is either used in the row, column, coloring, sizing, or labeling portions of a worksheet, the user will have the ability to right click on the field, and select "edit aliases".

A pop up will open with every value in the field (column) in a row, and next to the original value the user can type their updated alias and save.

For every single occurrence of that value in that workbook will now be updated. Please note that this is data source specific. This means that if a workbook is tied to more than one table (data source) that has the same field, then the user will need to alias or rename the value in every separate data source.

[viztips]

Combination Charts in Spotfire

Combination charts of a bar and a line in Spotfire can be a little tricky to create. First, insert the chart, and choose two measures on the value axis (y axis). Scroll to the legend, where it shows the two different measures with a little icon of a bar next to it (similar to the sprint logo). Click on the bars, and then you can select the line graph to change the marking to a line. Chances are if the category axis is a continuous variable (for instance a date; there's an infinite number of possible data points between each marking value), then you have very skinny bars which does not look appealing. If this is the case, change the variable so that it is no longer continuous (properties pop up --> categorical axis and there should be a setting there) and in the appearance window of the properties pop up you should be able to now adjust the width of the bars.

[viztips]

Connecting Spotfire to Oracle

Spotfire connection strings to an Oracle database are slightly less straightforward than connecting to a SQL server dbs.

Inside SQL developer, in the pop up for connection properties, a connection string is generated on the top left that contains a concatenation (merging together) of the user name, host name, port, and SID. In that connection string, the user will see that after the user id, the string is as follows: hostname:port/SID (please note the colon and backslash). This is the correct string to input in the server name box that pops up in Spotfire.

A sample below shows how Oracle itself generates the string.

[viztips]

Hiding sheets in Tableau

Unlike in Spotfire or Qlikview, it is possible to show or hide visuals in Tableau based on a user inputted parameter (such as a dropdown list). In essence, the developer can switch out visuals based on a drop down menu, which is useful in the event that a different chart type needs to be displayed or a chart from a different data source needs to be displayed in a given slot.

Let’s say I have graphs 1A and 2B that I want to be able to interchange.

To do this, first create a parameter (right click in the parameters box) and input the dropdown option values. For now we can input values “1A” and “2B”, and I called the parameter showtable. Then, create a calculated field and put in the parameter name and no other value (i.e. showtable). Make sure showtable is set to 1A. Drag the newly created calculated field, let’s called this showtable_calc into the filters box of the visual 1A and check “1A”; that should be the only option available. Click out of the filter control box.Then select another value of the dropdown menu - 2B. Go to worksheet 2B and drag the calculated field onto the filters panel again; now the only value should be “2B” in the resulting pop up. Repeat for as many show and hide sheets as desired.

Place visuals in a layout container on a dashboard. The layout container makes it such that if the worksheet is automatically fitted, the worksheet will expand to fill the area (i.e. contains sheets in the layout). Enable the parameter on the dashboard by hovering in the top right corner of a worksheet and clicking the upside down triangle. You should now see a “show parameter” option with a sub menu, and select the showtable parameter we created.

Now as you toggle through the dropdown, the different worksheets should appear in the layout container. 

[viztips]

Bringing marks in scatterplots to the front in Spotfire

With large data sets, scatterplots can get very cluttered with marks overlapping one another. If there is a particular subset of data points that should be brought to attention, then first color the group of markers using an existing or calculated field, and secondly change the size of the markers very slightly (such as by 0.01 difference) on the same colored field.

Let’s say for example that I have customers that are classified as low, medium, and high risk or not classified. I can color the marks on a scatterplot for each risk group differently, but let’s say we want to bring high risk points to the front of the chart.

The best way to do this is to create a calculated field where for each unique value (most likely a strong), assign a numeric value to the field of interest. A sample field would look like this:

“Case risk_group When “not classified” then 1 When “low risk” then .99 When “medium risk” then .98 When “high risk” then .97 End as [risk_group_size]”

 A case statement is a clean alternative to writing a series of if statements, especially if "elseif" syntax is not available. 

Then use the color and sizing tabs on the properties window (right click on the visual and hit properties to adjust visuals). Make the coloring by risk_group and the sizing by risk_group_size. To get the marks to appear as close in size as possible, you can adjust the range of the sizing wider. Smaller values will appear further in the front.  

[viztips]

General guidelines for data viz – 3 key tips

#1: Always group together related analysis. Make sure that users are reading graphs from left to right or top to bottom. You wouldn’t want two visuals with regional analysis placed far away from one other.

#2: Always make sure there is a business case for each graph. Confirm with the client which columns or fields are important; would a business make decisions based on region or based on employee? What business question does each graph answer?

#3: Make the dashboard interactive. Think about possible segmentations of the data that would be interesting to the client, or areas in which customers would like to input and test data values. 

[viztips]

The Spotfire OVER() function explained

An over function allows a person to aggregate (by this I mean perform a sum, count, max, min, etc) information in a column next to detail or “drilled down” information.

  This is useful for many reasons. For an example let’s think of employee sales in different regions:

-       I would like to show each employee’s sales next to the region’s average sales to see whether the employee is selling more or less than average.

-       I would like to rank the regions by total sales volume.

And so on.

  Generally in Spotfire, I insert a column into the table to first calculate the values and then troubleshoot. This is achieved by selecting “edit” on the top toolbar, then “column properties”. In the pop up window, I then select inserted column, and a box appears to enter a custom expression.

  Example syntax is as follows: sum(sales)over(region) or aggregationdunction(measure_column)over(dimension_column).

  Measure: a calculation input; a value that the user would want to add, count, etc.

Dimension: a means of segmenting data or a category

These classifications are dependent on how the column or field is used and are not inherent to the column itself. For instance; a region ID can be considered a measure if we are counting how many IDs exist, but is a dimension if we break down sales by region.

  The dimension field that is referenced in the parenthesis of the over() function means that every unique value in the dimension field is considered for the aggregate. In the example, every time the record “west” is mentioned, the sales value in the row is summed to other rows that have “west”.

As a result, the column created will have the same value, in this case sum of sales, for all  rows that have a region ID of “west”.

  If the objective is to find aggregate information over unique pairs, for instance sales of all employees in a specific region and state, first create a calculated field that concatenates  the two dimensions together (i.e. westcalifornia), then reference the calculated field in the over function.

[viztips]

Introductory and Advanced Tableau training videos can be found here in the free on-demand section.

[viztips]

Delivering Dashboards

One of the biggest questions I get about data visualization in the industry is how to "deliver" dashboards to clients. We are all familiar with emailing files such as Word documents, Excel spreadsheets, and Adobe pdf files, however delivery of data visualizations built in tools like Tableau and Spotfire can be a tad more complicated. Some options are below: 

Tableau: Multiple delivery methods with and without licenses

No license - Download free version of Tableau Reader: Tableau Reader is a program that takes about 5 seconds to download. The user is able to open a read-only version of a Tableau packaged workbook. A Tableau packaged workbook is a workbook with data extracts embedded into the file.

License later - Download free trial of Tableau Desktop (and/or activate a copy). Tableau Desktop provides the user with read and edit rights to the workbook. Note that this can be a hassle later if the client has not purchased a license, and that there is added risk that the client can corrupt the file with a well intentioned edit.

No license - View through Tableau Server. Some companies have set up their own servers where Tableau workbooks can be viewed through a browser. This is a clean alternative because no software needs to be downloaded, but has additional costs associated. This method is also nice because different user groups can be assigned different permissions. 

Spotfire: It is recommended that all users have the Enterprise Player, with rights such as saving analysis files to disk or to the library, opening analysis files from the library, opening linked data, and exporting to various formats. Additionally with the enterprise player, other licenses include:

Spotfire Web Player: user can view analysis in web browser

Spotfire Professional: full spotfire client with reading and editing rights