Monthly Archives: January 2015

Tableau in the office – episode 2

Time for another Tableau in the Office post. A quick viz that I worked on was for a department head that wanted to see how much a service had declined in recent years. The decline is due to advances in technology, therefore the service becoming obsolete. I took a look at the data (sample data used here) and created a simple line chart by plotting Quarter (Continuous) with Quantity.

ScreenHunter_01 Jan. 31 15.22

This is pretty straightforward. But I wanted to show the total percent decrease for 2014 Q4 since 2013 Q1. This is where I employed a Table Calculation. Table calcs in Tableau are extremely powerful. They can range for very simple to exceptionally complex. I try to read as much as I can about table calcs and encourage others as well.

I created a calculated field that used the LOOKUP function. This is a great way to isolate exactly what is needed as you can specify the offset. The formula is written as LOOKUP(expression, offset). So LOOKUP(SUM(QTY), FIRST() + 3) would return the fourth value in my partition.

Since I only needed the first and last values, I didn’t require an offset. Here’s how my formula looks:

ScreenHunter_02 Jan. 31 15.33

In pseudocode: (QTY at 2014 Q4 – QTY at 2013 Q1)/ QTY at 2013 Q1

I formatted the value as a percentage and placed this calculated field in the Detail shelf so I can use it in the chart.

ScreenHunter_03 Jan. 31 16.02

Right click on the last mark, select Annotate > Mark

ScreenHunter_04 Jan. 31 16.05

Edit the annotation as desired.

ScreenHunter_05 Jan. 31 16.06

Here’s my final viz. Simple and effective in illustrating the decrease of a service over the last two years.

ScreenHunter_06 Jan. 31 16.08

Thanks for tuning in!

Share Button

One year in

One year ago, Tableau declared January as Data Blogging Month. As a fan of the data community, I was inspired by that declaration to join in and thus, Data Knight Rises was born with this first post.

So a year in and I must say, “Wow!” Time sure does fly when you’re being a data geek. I have really enjoyed being part of this community. This blog has allowed me to e-meet some great people out there, with hopes of getting to meet them in person as well. The data community further inspires me to do more. I want to say Thank You to everyone who have supported me, taught me, and participated with me during this past year. Some of my most satisfying moments have been a simple thanks comment left on a post. It’s great knowing that what I have produced has made someone’s day easier in their data needs.

These site stats might not blow you away, but I’m really proud of what I’ve been able to accomplish this past year.

ScreenHunter_01 Jan. 25 13.24

So what’s next? There’s so much I want to accomplish, which I believe is a very common sentiment among other data enthusiasts. In order to be more organized, I feel like I should layout some goals for 2015.

  1. Continue learning and sharing Tableau and Alteryx tips/tricks/wins that I come across.
  2. Dedicate time to Python. I really want to get more into this language as its potential is vast in my professional and personal life.
  3. Community involvement. There are a plethora of data related gatherings in the DC area that I’ve been meaning to check out. The ones I see are from Data Community DC. Let me know if you know of others.
  4. Have fun! I never want this to feel like a chore or homework.

Thank you again for being a part of my journey and may your 2015 be as fulfilling as you intend!

Share Button

Quick Alteryx Win

So I first wrote about Alteryx last year, with the following post, when they invited me to speak at a local joint event with Tableau. If you’re unfamiliar with Alteryx, I suggest hitting the Google because it can make your life very easy.

At my company, we use a lot of MS SQL Server and all that it bundles. This includes SQL Server Integration Services. SSIS is a powerful ETL tool that we leverage to move large amounts of data between systems. But every time I launch Visual Studio and try to make a simple task, I inherently receive error messages that I have no idea what they mean. Yes, SSIS can do a great deal, but it feels like unless you have months and months of training, it is not a friendly and intuitive tool.

My task was to update tables in my SQL datamart that were once stored on a Lotus Notes database but now are in SharePoint as lists. I don’t know why I even opened Visual Studio to attempt this. But needless to say, it quickly frustrated me and then I thought, “Hmm, wonder if Alteryx can connect to SharePoint lists.” Sure enough, it can! I can’t stress how easy it was for me to build a module that would download the data from the each list and push it to my SQL datamart.

So here’s what I did. First, go to the Connectors tools and drag SharePoint List Input tool to your canvas.

ScreenHunter_02 Jan. 15 16.04

Next, complete the Properties for the tool. The SharePoint URL is everything before the /Lists in your url. Input your User Name and Password. Then pick the List you want from the drop down.

ScreenHunter_04 Jan. 15 16.09

I recommend sticking a Browse tool to check you’re correctly downloading what you need.

I then used the Select tool to only capture desired data, rename columns and change their data types. The Select tool is fantastic. It let’s you really narrow down what you need and how you need it. Have you ever done a Transformation task in SSIS? What a pain in the butt.

ScreenHunter_06 Jan. 15 16.23

Finally, each list has an Output tool to my SQL datamart. I really enjoy the flexibility outputting to SQL provides. Notice how you can add Pre and Post Create SQL statements into your module to save you time elsewhere.

ScreenHunter_05 Jan. 15 16.19

I repeated the process above six times, one for each list. Here’s what my module looks like when it was done. Clean and easy!

Alteryx SAP

This was such a great quick win for me, I wanted to share with you all.

Share Button