A video version of this blog is available here:

Typically, the customization of Top N filters in Tableau involves allowing the user to pick the number of records, or N, that shows in a visualization. What if we could add even more customization by allowing the user to pick the field that the Top N is calculated from?

Well good news: we can, and you’ve come to the right place to learn how to do it!

Before we dive into the details of how, let’s first show the results. Below is a screen share of the Tableau Sheet with the visualization and relevant parameters. You can see that selecting a field from the Top N Field Parameter not only changes the field that the top manufacturers are being calculated by, but also the sort on the visualization:

This allows the user to toggle between different fields to learn more about the data, as well as see different perspectives of the same data. It also allows the user to determine their own hierarchy of importance for these fields rather than having the top manufactures by a single field be set by the developer. For example, the sales manager at this superstore may want to know what manufacturers are the most popular by number of orders, but the order fulfillment manager is more interested in knowing which manufacturers have the longest processing time. They both can utilize the same view to answer those questions.

Let’s Recreate It!

1. Create Visualization

In this example, the visualization has a series of dual axis charts that show fields that are relevant to the chosen dimension: in this case, manufacturers. Make sure that the fields that you want the user to be able to select between are shown in the view. This will make the filtering and sorting capabilities clearer to the user.

2. Create Top N Field Parameter

You’ll need a parameter that has a list of the fields that you want the user to be able to select for the Top N filter. These don’t have to exactly match the field names of your data, but be sure they display how you want the user to see them. The screenshot below shows the details of our Top N Field Parameter for this example:

3. Create Field Filter Calculated Field

Using the Top N Field Parameter you just created, create a new calculated field that ties the field from your data with the parameter value, as shown below. This is just telling Tableau that when our Parameter value is the string “Profit Ratio,” we want to return the actual Profit Ratio field, and so on. Note the SUM, COUNTD and AVG aggregation functions in the formula. These should match the aggregation of your measures in the view:

4. Create Top N Filter

Pull the dimension you’d like to filter onto the Filters card and click the Top tab. Here, add the Top N Filed Filter Calc as the field. The aggregation box should autofill to “Custom.” Be sure to add this filter to context so it is above other filters you may have on the view. Below is a screenshot of what our filter menu looks like:

We also used the trick that allows the user to select the number of values shown, as highlighted in this blog.

5. Set up sorting

To set up the dynamic sort, right click on the dimension (Manufacturer in our example) and change the Sort By option to “Field.” Select descending for the sort order and add the Top N Field Filter Calc to the Field Name. Again, the aggregation should auto fill as Custom:

6. The Results

Be sure to test that changing the field parameter changes the top dimensions and sorting as expected.

Woo hoo! Just like that, you’ve elevated the Top N filter and added a layer of customization to your Tableau Dashboard. If you ever need help with your Tableau dashboards, or data visualization in general, feel free to reach out!

The post Elevating Top N Filters in Tableau: Dynamic Field Swapping and Sorting appeared first on InterWorks.

As a Marketing Research and Analytics student at Oklahoma State University, I find exploring a new survey platform very exciting. When I was given my first Typeform project, I knew I needed to start getting familiar with the Typeform interface.

Exploring Typeform’s Capabilities

To begin to understand Typeforms uses and capabilities, I first visited the Typeform Help Pages that Zion Spencer, Global Customer Engagement Manager, suggested to me. The help page is a great resource for new users, providing written guides and videos that appeal to different learning styles. I personally found the videos very helpful in kickstarting my understanding of Typeform.

Alongside watching the Typeform Help videos, I created a sample survey to gain hands on experience. I prioritized two objectives while making this sample survey: I wanted to make use of Typeform’s logic functions and use different types of questions and design layouts. This optimized my learning path.

For my sample survey, I created a decision-based game (The Quest Game) that showcases my favorite Typeform features. It also shows a different use case for the platform.

My Favorite Capabilities          

Personalization:

Compared to competitor survey platforms, I would rank Typeform high on personalization capabilities. You can add images and videos to your survey in Create mode using the Question tab in the righthand menu. Once you add your media, the menu below allows you to make changes to your image placement, which automatically moves the other aspects on your question/ending to fit:

Alongside moving the image, you can change the focal point to crop your image as you’d like. This is demonstrated in the video below:

More awesome personalization features that Typeform supports are the Media Gallery and customizable themes. The Media Gallery is filled with many pictures and videos for you to fill in your private or commercial Typeform without having to worry about copyright issues. Images from the Media Gallery also automatically fill in the alternative textbox, boosting accessibility while saving you time. Typeform’s Theme functions allows users to use a customized theme for their Typeforms to save time and be consistent. When using a theme, your background, buttons and text will be automatically formatted.

Logic:

Though other platforms have logic capabilities, Typeform has two ways to build and view the logic you’ve created. The first way is similar to other survey creators. You can build logic by building it out using a list. You can branch questions by selecting the answers and outcomes. For example, the below is a screenshot of a question on The Quest Game:

Personally, I find that their Logic Map feature is more helpful when creating complex branching across many questions and endings. This feature is another way that Typeform takes different learning styles into account. Below is the logic map of The Quest Game. As you can see, the map is a more visual way of showing the question path:

Overall, with the personalization and logic features, I find Typeform to be easy to learn and user friendly.

The Quest Game

Brainstorming in FigJam

I tend to start my projects by brainstorming in FigJam, an online whiteboard tool. In this case, I used FigJam  to map out the storyline of the game. Below is a snapshot of my FigJam board. Don’t worry, the endings are blurred:

As you can see, the diagram I created is very similar to Typeform’s logic map. This made it easy to recreate in Typeform.

Applying What I’ve Learned

The Quest Game utilizes both the personalization and logic capabilities that I highlighted above. These capabilities make Typeform a great platform to create customer satisfaction surveys, feedback forms and net promoter score surveys. You can also use the platform in a creative way to make a fun decision-based game. I encourage you to think outside the bounds of the software and platforms you use and see what else you can create.

Make sure to check out the The Quest Game here. Stay tuned for another blog post diving into the results!

The post The Quest Game: Exploring My Favorite Typeform Capabilities appeared first on InterWorks.

The Day Before: Bringing our Snowflake Data into Tableau

As the week began, we entered the development stage of our big intern project. My role thus far has been preparing for development by creating wireframes for the new dashboards.

For this project, we used a Snowflake-held data source which we would pull into Tableau and ThoughtSpot for development. The main table is rather large, with almost three billion rows. Naturally, I go for an extra-large snowflake data warehouse to cut loading times down while pulling the data into Tableau.

Everything started well. I recreated the joins of the star schema to the main table and the corresponding dimension tables in Tableau and made my first dashboard. As I continued developing, I wanted to take a deeper look into my joins and make sure they were as expected. In the process, I joined another large table to the main table. I quickly realized I chose the wrong table and canceled the query. Soon after, I left for the day and thought nothing of it again… until we realized the next day.

The Mess Up: A Mega Join Causing Long Snowflake Queries

First thing in the morning, I received a message saying that our project’s warehouse had used over seventy percent of our usual monthly usage on this internal account. We were only ten days into the month at the time. My team and I met to discuss possibilities and solutions. Initially, we were not aware of the effects. We started to see many more messages pop up from different InterWorkers. This is when we started to realize the scale of the mistake since it affected many other teams using the internal account as well. As we were brainstorming what could’ve caused this many credits to be used, I received a message with a screenshot of two fourteen-hour queries that ran on an extra-large warehouse, created by me.

As soon as we located the issue, the questions turned into solutions. Many InterWorkers came to me with assurance and positivity. Ty Ketchum, a Delivery Lead, brought our attention to the problem queries. In Ty’s message, he made it clear that everything was okay and fixable. This immediately calmed my nerves. With a quick meeting with Brooks Barth, a Service Lead, we were able to find solutions and prevent future hiccups. Brooks was able to dissect the SQL query to determine that the join I thought I canceled kept querying in Snowflake. Since I didn’t use the proper join keys, Snowflake was trying to join two large datasets that would ultimately create forty-five trillion rows (That’s 45,000,000,000,000 rows, for context.) The fourteen-hour queries ate around five hundred Snowflake Compute Credits in two days.

The Takeaways

Checking Snowflake Query History

After the incident, I watched the query history in Snowflake like a hawk. Though constant checking is not necessary, it is important to check that queries are running and canceling as expected, even if it’s just for your peace of mind. To check the query history on Snowflake, navigate to the Activity tab on the menu on the left of the interface. Then, press the ­”Query History” tab as shown below. On this page, you can filter by status, user, date, and much more:

Understanding Snowflake Warehouse Sizes and Credit Consumption

Another takeaway I learned is to switch to the lowest Snowflake warehouse size that is functional for your task. Once I pulled the data into Tableau, I could’ve switched it down to a medium or small warehouse. This would’ve minimized the credits that were used in this incident. To learn more about Snowflake’s usage, check out Holt Calder’s blog: “Introducing the Snowflake Data Cloud: Modern Data Warehouse.” For understanding how credits correlate to cost, see “Understanding Compute Cost, a Snowflake Guide.”

Using All Join Keys when Joining Data in Tableau

Aside from Snowflake’s capabilities, I learned a valuable lesson to use all join keys needed for a seamless join. In this case, I pulled in a table that caused a mega forty-five trillion row dataset to be created. If done correctly, the row count would’ve stayed close to the original three billion. The picture below, posted on Tableau’s help forum, defines a Left Join.

Since I didn’t use all of the necessary join keys, the data from the right table was duplicated… a lot. The graphic below is an example of this event:

In this example, the join clause tells Tableau to add the rows from Table 2 to Table 1 when the values in the “Movie Name” column match. When only joining on this key, Table 1 has duplicate data in the new table. In my case, this happened on a much larger scale.

If I wanted to join the tables, I should’ve used multiple join clauses that would’ve prevented the duplication of rows. In the below example, the table that is created using two join keys, “Movie Name” and “Date,” does not have duplicated data and reads as expected. This is called a composite primary key:

To learn more about types of joins and how they work, look into the Combining Data with Joins blog by Zac Heacker.

InterWorks’ Attitude Towards Mishaps

Of the many lessons I learned, one stood out to me the most: Though for a moment I thought the world might end, my coworkers kept supporting me through the entire process. Not only was I met with empathy and kindness, but also solutions and suggestions. During our onboarding process, we were told that mistakes are just opportunities to learn. Months later, I know that InterWorks truly practices that motto.

The post What I Learned from Totally Messing Up Joins and Snowflake Credits appeared first on InterWorks.