Skip to main content

Category: Uncategorized

It’s a Witch Hunt @ VizClub!

Emily Chen

Quick notes to recap:

  • We’ve finally moved to a pub (ode to true VizClub fashion). Next time we’ll bring a pocket projector so we can ALL see the screen.
  • Our original dataset on weed sales/activity in Denver didn’t pan out (our meeting was on April 20th)
  • We differed back to Sophie’s suggestion on a dataset about the witches of Scotland

Gotta be honest, Sophie really delivered on this dataset. Despite the strange and obscure topic, the dataset is robust. Not only does it span almost 300 years, but comes with 72 pages of documentation/definitions and schema map. It even has the lot/lats of where they were accused and case notes.

Really cool right? Even the case notes are old-timey and creepy! Really cool right? Even the case notes are old-timey and creepy!

As a side note, it doesn’t seem like Tableau recognizes Scotland’s county’s at all. At first Andy suggested this may be the old recorded name… but then was corrected that its still named that today. #everythingisoldhere

Here’s where we ended up:

Surprisingly, there are male witches?

The chart just above this is a % of total split between male and female for every year in the dataset. I am surprised that even the “low season” of witch hunts, there can still be high proportions of men persecuted.

Do witches run in families?

Yes. The dataset includes first and last names as separate fields as well as each year they were accused. We created a word cloud and ended up concluding that there may have been 3 generations of Thomsons that were accused of being witches.

Corroborating the data with the outside world

Mara was on her phone furiously doing research and asked us to look up tidbits of info on Scottish witches and looked up Isobel Gowdie. I always find it to be a surreal experience when a real life story is reflected in the data. However, the team ended up getting sidetracked and started entering random names into the search filter.

Sizing for Mobile

You might have noticed that there are lots of interesting details I’ve mentioned here that didn’t make it to the final viz. While not an explicit talking point, I think we all agreed that we’re reading and looking at our vizzes on our phone, hence the mobile size. Maybe next time we’ll be able to add things more scroll-y style a la a Matt Francis, but it was 9:30 (time to wrap up!). I expect we’ll be doing more of these in the future.

To Wrap Up

The night rolled out in a completely unexpected way – from analyzing weed sales in Colorado, to then exploring the world of witches in early modern Scotland. You really never know where you’ll end up at Viz Club.

Oo and thanks to the Pauls for being good sports. That was all Andy.

How to Scale Tableau User Filters in 5 Steps

Emily Chen Leave a comment

I’ve been working on a sales dashboards where I am visualizing a set of transactional data. One of the requirements was for Tableau Server to recognize the user and their territory upon login. Easy peasy with Tableau User Filters right?

I want to show you how you can do this easily if you have the following complications:

  • Multiple sales reps to 1 region– joining these two datasets will multiply your records (and over-report sales dramatically!).
  • Have a large number of users (I have over 100 reps) and no time to update it manually and constantly as the team changes.
  • Lots of sheets in your workbook and did not want to spend the time adding the user filter set to every sheet.
  • You also dread Tableau’s inevitable “*” error on a blends for your joining table.

Big thanks to the Great Chris Love for this tip!

Step 1: Find Your List of Users and Pivot your Data

You’ll need 2 pieces of data – assigned Tableau Server Usernames, and their respective regions. I’ve mocked up a dummy data set to help illustrate with our favorite retail store – Superstore. Usually the USERNAME() function will be a reflection of your organization’s system username if your Tableau Server is using Active Directory to authenticate/login your users.

As you can see, we have 4 sales regions at Superstore, and 2 sales reps look after each region.

Quick view of my raw dataset

Tableau Usernames for Tableau User Filters

We need to pivot the data so it ends up as a lookup table, but concatenating usersnames to sit in 1 cell. I did this bit in Alteryx.

Alteryx Workflow for Tableau User Filters -Here’s what my workflow looked like with annotations. Alteryx Workflow for Tableau User Filters -Here’s what my workflow looked like with annotations.

BONUS TIP! Did you know you could annotate your tools instead of using the comments box? Much easier when you’re moving around tools in your workflow.

Step 2: Join your lookup table against your transactional data and output your data into Tableau!

Step 3: Connect your output (.tde/.csv) into Tableau and login to your Tableau Server

Connect your data source and sign into Tableau Server. Connect your data source and sign into Tableau Server.

Step 4: Create the following calculation:

Since our usernames are concatenated to exist in 1 cell for every territory, this creates the association between 1 territory and many usernames upon a user’s sign-on.

Step 5: Create a data source filter using the calculation above and Select “True”.
The calculation will look up the username and will filter to the territory specified in your lookup table (now joined against the transaction data)

That was the last step!

You can use the navigation at the bottom to change users views just as you would with a set recommended by Tableau. Hopefully you’ll use this clever method of data pivoting to bring a better UI experience to your Tableau Server users.

If you’d like to download this workbook to see the other calculations, visualisations I have used, you can access the workbook here. Note that the filter will not work with your Tableau Server so I have removed it to avoid confusion.

XML Parsing for Beginners with Alteryx

Emily Chen Leave a comment

Recently I’ve come across using XML parsing with web scraping and combining many XML tables. Because of its syntax, I wanted to outline some basic rules about XML and how to work with XML files within Alteryx.

XML files are different from flat columnar tables (the ones we’re used to!) because instead of headers and rows, the data is nested within tags, where the field headers are identified for every record with <> brackets. Below is the example from  Reading XML by Alteryx where the same data is presented as a columnar table and the other in XML. Because of its nested nature, it needs a bit of digging/examining the original data to figure out which tags to target and parse.

Example 1: Parsing from 1 XML file

Let’s go through one recent example I was working on for a client – parsing clinical trial data from XML files. Let’s say I wanted to 2 pieces of information – the name of the trial and when did it start. I like to take a look at the XML file to see where is the information located so I can figure out the best way to parse it.

I’ve opened the file in notepad++ and highlighted the tags which show which pieces of data I am looking for. Note that the root element in this file is “clinical study”, and it just so happens that <brief_title> and <start _date> are child values.

What happens when I select this file in Alteryx’s input tool?

Alteryx’s default settings is to pick up only 1 set of the file’s child values, so there’s a lot of information not yet coming through.

I can bring the information we are trying to parse into Alteryx by selecting “Return Root Element” and  “Return Child Values” as the information we are looking for is only 1 layer beneath the Root Element.

Simply add a select tool for the fields we wanted to parse and voila!

But what if I want to parse data that is nested deeper within the XML file?

What if you wanted to find who sponsored the clinical trial?

The data is nested within the <agency> tags, so its not a child value of the root element. This is where can use the Outer XML configuration to bring in all XML tags within the root element. From there, we can parse out the <agency> tags with the ..

XML PARSE TOOL! XML PARSE TOOL!

Simply select “Return Root Element”  and “Return Outer XML” in the input tool so Alteryx can identify both the root element and its respective nested tags.

Then add the XML Parse tool and configure the tool to look for the <agency> tag within the new field “clinical_study_OuterXML”.

I’ve got the results window above with the <agency> data parsed!

XML parsing requires a bit of evaluating the dataset and identifying where your data is located. Because of its nested nature, you might have quite of a few XML parse tools in your workflow. But the same concepts can be taken into web scraping with Alteryx so learning XML and its tagging system has transferable value!

I hope this has been a clear introduction to working with XML files. Let me know if you have any questions in the comments section below.

LODs of Fun: How to Filter Moving Up a Level of Granularity

Emily Chen Leave a comment

In my spare time, I’m working on a viz on global immunization rates for 1 year olds from the WHO. The dataset has 2 levels of geography – country and region.

This is quite simple to do in a “top-down” scenario (for instance, region to country), but how do we get this to work the other way around?

What if I wanted to select a country and see what other countries are in the same region? Specifically, for the map to zoom in on its respective region?

First off, there are a couple of clues we can pick up from this problem:

1) We can’t use a filter on country, because it will obviously filter to the selected country.

Rather we need to filter to 1 level above this, region. In my mind, I’m already sensing we’ll have to do an “if” statement of sorts.

2) Since we cannot create a filter and will have to use the if statement, we’ll be using a parameter.

Big thank you to Craig Bloodworth for helping me work through this problem 🙂 

How do we get this action to work?

Step 1: Create a parameter that determines which region was selected.

How to create a parameter:

Step 2: Create the calculation which drives the parameter:

Here’s a close up view of the calculation:

In this instance, when we select a country in the parameter, the IF statement either returns Null or the Region. We can see that working in this “sanity check” table.

You might be wondering why we need the MAX() function. Indeed, if we were to take out the max function we would get the same result in the table. Why even use it to begin with?

The reason why we cannot directly use a calculation where we specify the country, but ask it to return its respective Region is because it will only bring back data on the Region related to it, almost thinking it is the only stance of this Region. i.e. If we only select “Canada”, it will only retrieve regions where country=”Canada”. Since this is at a lower level of granularity, it will only bring back 1 stance of “America” within Region, instead of all instances of “America”.

Therefore, we need to create a scenario where the view is supplemented with extra context, achieved through a FIXED level of detail calculation.

However, Level of Detail calculations can only be done on aggregations and fundamentally, it has to be a measure. That means in order for us to access the ability to use a LOD calculation, we need to turn our parameter control function into an aggregation – our MAX() function. We will build the LOD calc in the next step.

This is the first time I’ve used a traditional dimension and processed it as a measure and aggregation. Totally turned my head upside down!

Step 3: Creating the filter calculation for 1 level above the detail in the view

Our first instinct here might be to create this calculation.

But of course it doesn’t work because our selected region is still an aggregation thanks to our MAX() function. We can remove the aggregation with the { } brackets, creating a FIXED LOD calculation. Remember it has to be fixed in order for the the data to go past the filter. Yay to the order of operations!

Correct calculation

Bring the calculation to filter and voila! The functionality will start working in the table.

It looks even better with a map 🙂

lods

Happy Vizzing!