﻿ Tableau Certification Preparation Guide Practical blog for Viz Enthusiasts
Jun
28
2016

# Tableau Certification Preparation Guide

This article explains Step by Step solution for all the sample question for  DESKTOP 9 QUALIFIED ASSOCIATE-Exam.

Theory/Information:

So many of my friends are preparing for DESKTOP 9 QUALIFIED ASSOCIATE-Exam but there are very few questions which are available over the internet. Everyone is struggling and finding it difficult to prepare for the exam. There are only 13 questions which is listed on DESKTOP 9 QUALIFIED ASSOCIATE-Exam Preparation Guide and I have already provided the solution in my website. While doing my research, I stumbled across one website (http://learningtableau.com ) which contains some sample tableau certification questions which will surely help you to prepare for Examination. So I thought of providing step by step solution of each question. Here’s comes the solution for you guys.

The questions below use the dataset Sample – Superstore Subset (Excel), which can be downloaded here.

# Qualified Associate Certification Practice Quiz 1

Question 1:

Step 1:

Drag a Ship Date to the Filter shelf and select #Month / Year option. Select July 2012 and Press OK.

Step 2:

Drag Product Container on the Rows and Sales on the Text Labels. As per the Question, we need to find the “what percent of sales were sent in a Large Box”.

So there are two ways through which we can calculate the % of Total Sales.

Right Click on Analysis-> Percentage of -> Column. It will give you % of Total sales for Each Product Container.

Right click on Sum (Sales) ->Quick table Calculation->Percent of Total.

Final output will be

Question 2:

Step 1:

Drag Product sub-categories on the Row shelf and Sales on the Text Label marks Card. Add Ship Mode on the Filter Shelf and Select “Express Air” and “Regular Air”

From the Filter Shelf.

Step 2:

Right Click on Sales –> Quick Table Calculation -> Rank. Currently Sales is computing using -> Table Across. We need to Compute Using ->Table Down which will help in getting the desired result.

Step 3:

Select the Regular Air and perform the Ascending sorting as described below. Now you will see second highest subcategory for Regular Air sales is ranked 5 for Express Air.

Question 3:

Step 1:

Double click on “State or Province” present on the Dimension data pane window. It will generate the required Latitude and longitude. In other words, Tableau will automatically assign geographic roles to fields based on the field name and a sampling of values in the data.

Change the Automatic marks from symbol to Filled map. You can also use “Show me” option for changing the marks from symbol to Filled map which is present at Top right corner of the tableau.

Step 2:

Drag “Product Category” on the Filter shelf and select “Furniture”.

Step 3:

Drag Profit on the color Shelf and select dark color as the stepped color as described below. Add State on the Text label and check the unprofitable states.

You will see Vermont is the only unprofitable state which is surrounded by only profitable states.

# Qualified Associate Certification Practice Quiz 2

Question 4:

Step 1:

Drag “Customer Segment” dimension from data pane window to Row Shelf and Sales on the Text Labels. Add order date as 2013 in the Filter shelf.

Step 2:

Create a calculation as per the Question “Sales numbers were expected to increase by 10 %”. Add the newly created calculation(Row Level aggregated calculation) and get the desired result.

You can also write View Level aggregation calculation Sum(Sales)+.1*Sum(Sales)

Question 5:

Step 1:

Drag Product Name on the Row Shelf and defined the Ship cost to sales Ratio calculation and put it into the row Shelf. Perform the sorting on calculation and get the desired result.

Question 6:

Step 1:

Drag Customer name on the Row Shelf and Profit on the Column shelf. Perform the sorting on Profit which will give you customer names who are having Highest profit.

Step 2:

Now we need to calculate his (Andrea Shaw) average shipping cost per order. There are two ways through which we can solve this question.

The Final result will look like the below image.

# Qualified Associate Certification Practice Quiz 3

Question 7:

Step 1:

Select Product category from the dimension pane and put it into the column shelf. Add Sales on the Row Shelf.

Go to Analysis-> Uncheck “Aggregate Measures”. Double Click on “Show me” and then select “Box and Whisker Plot”      .

Question 8:

Step 1:

Drag “Product Sub Categories” dimension from data pane window to Row Shelf and then drag the Profit to the text shelf. After that go to Analysis ->Totals -> Show Column Grand Totals.

Step 2:

Then our next motto is to find the overall average profit which can be done in two ways.

Approach 1: Go to Analysis ->Totals -> Total All Using -> Average .It will give you the overall average profit of all the Product Sub categories.

Approach 2: We can write the LOD expression for getting the same overall Average profit.

{ FIXED : avg ({fixed [Product Sub-Category]  : sum([Profit]) }) }

Step 3:

Create a calculated field which should show the below the average overall Sales number i.e. 526584.

Add this newly calculated Field in the measure values as depicted below and get the Desired Value.

Question 9:

Step 1:

Add a customer name in the Row shelf and sales on the column shelf. Right click on Customer name ->Filter-> Top Tab -> By Field ->Top 10 by sum(sales)

Step 2:

Create a set by using that customer name which is available in the Filter shelf.

Step 3:

Clear the Sheet and then add the new Set (Top 5 Customer Sales) in the Row Shelf. Add the profit on the text label and then do Quick table calculation ->percentage of Total

# Qualified Associate Practice Quiz 4 – Trendlines

Question 10:

Step 1:

Drag “Sales” measures from data pane window to Column Shelf and “Profit” on Row Shelf. Once the measures are on appropriate shelf, we need to disaggregate both sales and Profit.

Go to the Analysis (Present in the Toolbar) ->Uncheck Aggregate measures.

Step 2:

Right click on the View and show the Trend Lines

Step 3:

Then Right click on Generated Trend Lines and Select “Edit Trend lines” and select the Model Type = Linear.

Also uncheck the “Show Confidence Bands” and “Show Recalculated Line for Highlighted or Selected data points” checkbox.

Note: Selecting show confidence bands add upper and lower bounding lines based on the variation of the data

Step 4:

Hover over the Trend Line, a little pop-up window opens. Tableau shows the linear equation which provides the values for the coefficients (slope & intercept).

As per the question we need to check the R Squared Value. You will see your desired result.

Question 11:

Step 1:

Follow the exact Steps till the last which is mentioned in the above question and notice the Simple Linear Expression Y= mx+c.

You will get your desired result.Here Profit got increased by m times whch is 0.142809

Question 12:

Step 1:

Before solving the Question, let me give you an overview of Trend Lines.

Trend lines are statistical models you can add to your view in order to quickly derive predictions, correlations, and other insights.

It is very useful frequently used in simple linear regression to observe the relationship between two variables as well as predicting future values.The shape of the trend line explains the type of the relationship between the variables. For example, in the case of simple linear regression,The trend line is a straight line, which is represented by the mathematical equation of a straight line:

y = mx + c.

If you aren’t a statistics expert, focus on the P-Value and R-Squared (coefficient of determination). They help you evaluate the reliability and predictive value of the trend line plot.   If the P-Value is greater than .05, then the trend line doesn’t provide much predictive value. R-Squared provides an indicator of how well the line fits the individual marks.

if the R-Square value (say .001) is very low, then indicates that the plot doesn’t fit the marks very precisely. Tableau always does the best job in fitting the line to the plot, but if the marks are randomly scattered, then R-Squared value will be low. The combination of low P-Value and R-Squared value means that the trend line does not provide much predictive value.

Step 2:

Now Calculate the R squared Value for each Model Type and see which R squared Value is more because greater the R-Square value better will be the result.

Now as per the below image what we see, Polynomial with Degree 2 shows the Maximum Value.

# Qualified Associate Certification Quiz 5

Question 13:

Ans: Dimension is a field that can be considered an independent variable. By default, Tableau treats any field containing qualitative, categorical information as a dimension.It is basically a reference variables that give context to measures i.e Dimension often partitioned or  “slice and dice” the aggregate measures.
e.g. Sum of Profit by State or Average Order Quantity by Region.

Question 14:

Ans:Tableau automatically classifies your data based on whether it contains categorical data (dimensions) or quantitative data (measures).Most beginners believe blue pills and icons denote dimensions while green pills are used to display measures.While this is frequently the case, the truth is more subtle. Blue pills/ icons denote “discrete” fields. Green pills/ icons denote “continuous” fields.Dates can be both discrete and continuous but by default dates are typically treated  as Dimension.

Question 15:

Ans:From the Tableau perspective, hierarchies are groups of columns that are arranged in increasing levels of granularity.Each deeper level of the hierarchy refers to more specific details of the data and from the user perspective, hierarchies improve navigation and use by allowing the users to navigate from a headline down to a detailed level. This is a nice feature inside tableau because  hierarchy can reflect the users' understanding of the data and isn't determined only by the underlying data.

Question 16:

Question 17:

Step 1:

Add Sales measures on Row Shelf and Shipping Cost on Column shelf. Go to Analysis-> Uncheck Aggregate Measures Option.  Right click on anywhere in the View, Select Trend Lines -> Show Trend Lines ->Trend Model -> Linear.

Step 2:

Hover over the Trend Line and observe the Y= mx+c . You will see

Shipping Cost= 0.002015156*Sales+10.8468

Question 18:

Step 1:

Add Quantity Ordered new measures on X Row shelf and Shipping Cost on column shelf. Go to Analysis-> Uncheck Aggregate Measures Option.  Right click on anywhere in the View, Select Trend Lines -> Show Trend Lines ->Trend Model -> Linear.

Step 2:

Hover over the Trend Line and observe the predictive value .You will see P value () is less than 0.05

Question 19:

Step 1:

Add Unit Price on Row Shelf and Shipping Cost on Column shelf. Go to Analysis-> Uncheck Aggregate Measures Option.  Right click on anywhere in the View, Select Trend Lines -> Show Trend Lines ->Trend Model -> Linear.

Step 2:

Hover over the Trend Line and observe the R-Squared (coefficient of determination) which is equal is to .06 (6 %)

Calculations Quiz

Question 20:

Step 1:

Drag Region on the Filter shelf and select “South”, after that drag order date on the Filter and select Year as 2011.Put profit on the Text shelf and get the desired result.

Question 21:

Step 1:

Drag the Product Sub category on the Row shelf. Write a calculated Field which calculate Profit to Sales Ratio and perform the Descending sorting on it.

Question 22:

Step 1:

Add Customer segment on the Row Shelf and Customer ID on the Column Shelf. Take the CountD of the Customer id which will give the total number of distinct order. Double click on “Show Mark Labels”  to get it enabled.

Question 23:

Ans: When you write the Similar Calculation you will receive an error message: “Cant Logically ‘and’ Boolean and String Values. Tableau can only compare the expression if you assign [Shipping Mode] =”Delivery Truck”.

Question 24:

Ans:As per the syntax, Left Function Returns the left-most number of characters in the string.  Integer value should appear after the string as per the below image.

Forecast Quiz

Question 25:

Step 1:

Drag Date field from dimension pane to the column shelf and Value from measure shelf to Row Shelf. Convert the Discrete date into a Continuous Month date as per the below image.

Step 2:

Then Right click on the view Forecast-> show Forecast .This will generate a monthly forecasted Value.

Hover on the Selected Predicted value and note down the result.

Question 26:

Step 1:

Follow the similar steps which is mentioned in the above Problem. Here, we need to calculate upper value for the 99% prediction interval for the April 2014 forecast?

Let me give you an overview Upper and Lower prediction Interval value.

Upper Prediction Interval—Shows the value above which the true future value will lie confidence level percent of the time assuming a high quality model. The confidence level percentage is controlled by the Prediction Interval setting in the Forecast Options dialog box.

Lower Prediction Interval—Shows 90, 95, or 99 confidence level below the forecast value. The actual interval is controlled by the Prediction Interval setting in the Forecast Options dialog box.

Step 2:

Right Click on the view-> Forecast->Forecast options-> Show 99 % Prediction interval

Step 3:

Now add value measure in the Text label Marks card. Right Click Sum (value) ->Forecast Results-> upper Prediction value.

Hover over the Estimated April 2014 Forecasted value and get the desired upper prediction value.

## Related Posts

#### About the Author: Rajeev Pandey

I am a multidisciplinary designer working in data visualization, interaction design and innovation. Expertise in developing Tableau, Web focus based visualization and reporting applications. I have a passion for analyzing, dissecting, and manipulating data sets as well as, building beautiful dashboard. Naturally talented in communicating between technology and business needs. Diverse and experienced in plenty of different domains .I am quick learner who can absorb new ideas and can communicate clearly and effectively.I love creativity and enjoy experimenting with various technologies.

• The addition of questions are really good.

• Approach 2: We can write the LOD expression for getting the same overall Average profit.

{ FIXED : avg ({fixed [Product Sub-Category]  : sum([Profit]) })

question 8, is regarding sales , a  little misleading

regards

m123

• Regarding certificatio exam ? can we open google during exam or using calculations or google help to find information , i know this is not correct method but asking if this acceptable during exam ? can i open the help document during exam ?