Feb
20
2016

Tableau Certification Preparation Guide -Desktop 9 Qualified Associate-

This article explains Step by Step solution for all the sample question which is available in DESKTOP 9 QUALIFIED ASSOCIATE-Exam Preparation Guide.

Theory/Information:

DESKTOP 9 QUALIFIED ASSOCIATE-Exam Preparation Guide is a document which provides information on the structure of the exam, along with the knowledge and skills being measured. It will also guide you to resources to help you prepare for success. But inside this document, first few questions only contains answer and there are so many Tableau learners who are eagerly looking for 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.

Question 1:

1

Ans:  Join

Question 2:

2

Ans: TRUE

Question 3:

Step 1:

Drag “Customer Segment” dimension from data pane window to Row Shelf and then add an “order Date” on Filter shelf and select “Month/Year” of Order date which is –July of 2012-

3

Step 2:

Drag a Sales measure to the text label. Right click on the aggregated Sales measure and click on arrow sign then select Quick Table Calculation > Percentage of Total

3.1

Step 3:

Then match the % of total sales data for customer segment home office with the options available in the question.

4

Question 4:

5

Step 1:

Drag “Product Name” dimension from data pane window to Row Shelf and then add an “order Date” on Filter shelf and select “Year” of Order date as 2011.After that put region on Filter shelf and select “Central” and “West” checkbox. Also, put a copy of region to the Column shelf as well.

6

Step 2:

Drag a Sales measure to the text label. So for getting the Top 10 “product name” by sales, we need to add the “Product name” on Filter shelf. Once the Filter Pop up is open,

Select “TOP” tab >By Field > Top 10 by Sum (Sales).

7

The Final result will look like the below image.

8

Step 3:

Right click on the aggregated Sales measure and click on arrow sign then select Quick Table Calculation > Rank. As the default addressing is Table across, please change it into Table Down (Compute using -> Table Down).

9

The Final result will look like the below image.

10

Question 5:

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.

11

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.

12

Step 2:

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

13

Step 3:

Drag “Profit” on the color marks .It will generate the Profit range between -14569 to 81111.Now the Final result will look like the image which indicates “Colorado” is surrounded by only profitable states.

14

Note: (Just an advice): Filled maps can work well in interactive dashboards and have quite a bit of aesthetic value. However, certain kinds of analysis are very difficult with filled maps. Unlike other visualization types where size can be used to communicate facets of the data, the size of a filled geographic region only relates the geographic size. For example, which state has the highest profit? You might be tempted to say California, but are you sure that's not just because it is larger than Illinois? Which has more profit: Massachusetts or Texas? Use filled maps with caution and consider pairing them with other visualizations for clear communication.

15                                                     

Question 6:

16

Step 1:

Drag “Customer Segment” dimension from data pane window to Row Shelf and then add an “order Date” on Filter shelf and select “2013” as a year. Double click on Sales measure, it will automatically be placed on the row shelf.

17

Step 2:

Now as per the question, 2013 sales numbers are expected to increase to 150 %, so for getting the value, we need to create a calculated Field as below.

18

or

19

Question 7:

20

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.

21

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.

22

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

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

23

24

Step 3:

Now drag a region dimension beside Product Sub- category .After that create a calculated field by using the average overall Profit number i.e. 77202. Drag that calculated Field on the detail shelf and then add a reference line by using Avg Profit or by using the LOD calculated field. Then you will see “South” is the only region for which all Product Sub categories fall beneath the overall average profit.

25

26

Question 8:

27

Step 1:

Drag “Product Sub Categories” dimension from data pane window to Row Shelf and then create a calculated Field “Shipping Cost to Sales ratio” as below.

Sum ([Shipping Cost])/SUM ([Sales])

28

Step 2:

Drag this new calculated Field on Row shelf, convert the Field into discrete and perform the sorting on descending order. Also,change the Default properties of the measure to Percentage.

29

Question 9:

Step 1:

Drag “Customer name” dimension from data pane window to Row Shelf and “Profit” on Column Shelf and then perform the sorting on descending order. The result will look like the below image.

30

Step 2:

Define a new calculated Field which will calculate the Profit ratio as below and drag this Field on the Column shelf. After that Change the default properties of the measure to Percentage.

Sum ([Profit])/Sum ([Sales])

32

31

Question 10:

33

Step 1:

Drag “State or Province” on Column shelf and Profit on row shelf. After that drag a Region dimension on Filter shelves and select “central” as its option.

34

Step 2:

As per the Question, we need to find the State in the Central Region which has the highest distribution of profits using interquartile ranges and it can only be done by using the Box and whisker plot. So, click on “Show me” option and select “Box and whisker plot”.

35

Note:

The interquartile range (IQR) is a measure of variability, based on dividing a data set into quartiles.

Quartiles divide a rank-ordered data set into four equal parts. The values that divide each part are called the first, second, and third quartiles; and they are denoted by Q1, Q2, and Q3, respectively.

36

  • Q1 is the "middle" value in the first half of the rank-ordered data set.
  • Q2 is the median value in the set.                                                                                                           
  • Q3 is the "middle" value in the second half of the rank-ordered data set.

The interquartile range is equal to Q3 minus Q1.

Step 3:

Now again drag the “State or Province” dimension from Details marks area to Column shelf .After that go to analysis > uncheck “Aggregated Measures”. You will see “South Dakota” is having highest distribution of profits using interquartile ranges.

37

Question 11:

38

Step 1:

Follow the step 1 and step 2 which is mentioned for question 7. You can also use the LOD calculation.

39

Step 2:

Then create a calculated field as described below. Put the measure names on Filter and select “Difference to overall Profit” and “Profit” checkbox. Once the selection is done , drag the Measure values in text label and Measure names in Column shelf   

SUM ([Profit])-77202

40

Final result will look like the below image and you can see that Envelops sub-category is $31,069 below the average profit across all categories

41

 

 

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.

7 Comments+ Add Comment

  • Wow ..Well explained.. Can you post few posts on LOD expressions. Can you please let me know how to get through the examination, the study material ..

    • Thanks for your fedback. Will soon post on LOD Expression. Drop me an email, regarding all your certification question .

      • Hi Rajeev, i am preparing for tableau certification. could you pls guide me. i msg you in FB. Pls chack. this is my mahasundar12@gmail.com .

  • You have lot of patience in explaining what you know to others. Can you explain the 11 th question in the pdf, I am not getting answer for that question. 

  • Out of total sales in year 2013 .What is the proportion in percentage of Customers who first ordered in year 2010 ? can u please tell me how to solve this question

  • Hi Rajeev,

    I am planning to appear for the cert. Can you please suggest if there is any dump for the same.

  • There is no such thing (Dump) in Tableau .Please go through the Tableau concepts and read the Tableau manual completely

Leave a comment

You must be logged into post a comment.