Tableau Data Analyst – Exam 1 – Free

Solution

The correct answer for the given scenario is condition filter as it provides you an option to make logic to filter the data based on the business requirement.

Following article, explains well, when and how different type of filters can be used

https://help.tableau.com/current/pro/desktop/en-us/filtering.htm

Solution

ZN and IFNULL are the correct answer to this questions as shown in the images below from Tableau Knowledge base articles

Reference Articles

https://help.tableau.com/current/pro/desktop/en-us/functions_functions_number.htm

https://help.tableau.com/current/pro/desktop/en-us/functions_functions_logical.htm

Solution

Follow the sequence to get the desired result

•  Add the date to the columns shelf, Add the product line and the daily complains to the rows shelf
• Aggregate the date to the month and year
• From the Analytics Pane, drag forecast to the worksheet

Solution

The Rank value will be 3 and that can be verified by ordering the average value of the products sold against the employees.

For the verification, see the below image that clearly shows that the value for employee 4220 falls at the third rank.

We have simply sorted out the data based on the avg products sold field

Now for your better understanding, the same question now solved in the Tableau and looks like this

Created a Calculated field as shown below

And placed it on the Text on the Marks Card

For your better understanding the concept and clarification, see the same question with RANK_DENSE formula and its impact on the rank results.

This example has been given just to strengthen your concept for Rank related questions.

Likewise you can test your knowledge for other RANK functions

Solution

This requirement can be achieved by simply creating the aliases of the values in the State column.

Following link further elaborates the concept

https://help.tableau.com/current/pro/desktop/en-us/datafields_fieldproperties_aliases_ex1editing.htm

Solution

The trick in this question is that space is a default option for a field separator so the option to select space by giving space as a field separator is an invalid option. You do not actually need to give space as a separate.

For more details, refer the following link

https://help.tableau.com/current/pro/desktop/en-us/split.htm

Solution

The correct option is the distribution band.

In order to achieve the desired result following is the navigation.

Following screen appears. On this screen select the Distribution and in the computation value click on the drop-down as shown below

After it shows the values, you need to select the Quantiles value to have the desired output

So the right selection of the answer is Distribution Band.

Solution

Put Sales on the Rows shelf, Profit on the Columns Shelf and select State to put on the Detail card as shown in the picture below.

This will create scatter plot, now add a reference line

Add Order date in the filters and select the year 2013

And then select the following options

It will create show the standard deviation -1,1 as shown below

Clearly seeing the graph, there are 6 'State or Province' that fall outside the standard deviation for the year 2013.

Solution

The correct syntax for the given scenario is {FIXED: SUM (Population)}.

To know more about LOD in Tableau, refer the following link

https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod.htm

Solution

Only density legend and density parameter are floating. Rest all objects are not floating in the given chart.

See the properties of Density Legend

See the properties of Density Parameter for your reference

Solution

This questions need data transformation so we shall be solving it using Tableau Prep and following order will be followed

D - Open Tableau Prep

C - Create a New Column to concatenate the name for the UAE division

A - Union the Sales Data of both divisions

E - Join the Sales Data and the population data by using City as the Join field

Note: There are some further steps that should be involved in order to meet the requirement for example, in the given steps, it is not mentioned how to handle the arrangements of columns as Saudi data source contains the email column whereas UAE data source does not contain such a column.

This information is intentionally skipped to confuse the users.

Focus only on the steps that have been provided and do not think about the steps that are not provided in the question.

Solution

The most appropriate solution is to publish it on the Tableau Server and share the link with your organization employees whoever is entitled to see the data so the most appropriate option is

Publish the dashboard to Tableau Server and share the link to users

Solution

Resume suspended alerts

If an alert fails enough times, you'll receive a notification email that your alert has been suspended. There are a few ways that administrators or alert owners can resume a suspended alert:

• From the Tasks > Alerts area of Tableau web pages, an icon appears in the Last checked column to indicate that the alert is suspended. Select ... > Resume Alert to resume the alert.
• Click Resume Alert in the notification email to resume the alert. A notification will either allow you to resume the alert, or indicate that the view has changed and the alert should be deleted.
• From the Alerts panel of the affected view or workbook. To resume the alert from a view or workbook, select Alert to open the Alerts panel. An icon appears next to the suspended alert. Select Actions > Resume Alert on the affected alert to resume.

Alert owners will receive an email notification when the alert is working again.

For more details, click the link below

https://help.tableau.com/current/online/en-us/data_alerts_admin.htm#:~:text=Resume%20suspended%20alerts,-If%20an%20alert&text=From%20the%20Tasks%20%3E%20Alerts%20area,email%20to%20resume%20the%20alert.

Solution

States.Hyper is an extract file.

Solution

Click on Density Map Sheet and mouse hover to Density Table Sheet and click swap sheet button next to Density Table Sheet

Click on density map and then take your mouse to Density Table sheet, it will enable a swap option as shown below

Upon click, it replaces the sheets in the dashboard

Solution

This requirement can be entertained by Extract Refresh functionality and since extract is already published so you can configure the extract refresh frequency instead of creating a new refresh

Solution

A Parameter and a calculation field will be an ideal way to achieve the given requirement. Following article explains the same step by step

https://interworks.com/blog/2021/12/02/the-basics-of-sheet-swapping-in-tableau-desktop/

Solution

In the question, they have asked to make it work in a way that any click on the map should filter the data in the tabular side and if we click any other point on the map, the tabular sheet will reflect the data as per the new selection.

So in this case, we shall select the Filter dashboard action that should fire on the select action and should show all values when clearing the filter.

Solution

More than one refresh schedules can be configured for a published extract, so appropriate answer to this questions would be to add a new refresh schedule along with the existing one.

From Tableau Server, select the workbook, select refresh extracts and then add a new extract refresh schedule as per given requirement

Solution

Username() function contains the value of the logged in user and hence it will work find for the given scenario.

USERNAME() = Tableau_User_Name

Solution

Common Characters is the most feasible option for the given scenario

Solution

Change the mark type to density

Solution

You can use the background image in order to make your chart as watermarked.

Solution

As two sets already exist that provide the information for the Top 3 and Bottom 3 members, in such case, our job is simply to create a combined set to include all members from both sets.

Select both sets and create a combined set as shown in the picture and select the following settings

Now put the combined set in the filter shelf to see the desired result with 6 product sub categories containing top 3 and bottom 3 members.

Once Tableau data extracts are upgraded, It can not be converted back to previous format and when you open the work book that uses older version of .TDE, it must be converted to .hyper format before opening

Solution

Create a calculation to get the customer who have first sales in 2011

Place calculated field in filter and Sales on Text option in marks card to get the desired value

Solution

Place City on the view pane and it will automatically start showing the locations. In case it does not show just click on the view and set the following setting

Search the Winter Haven in the Map search and set the setting from Map Menu to Metric to show the distance in kilometers, default setting is Automatic which shows the distance in Miles.

Once you select the 34 KM radius, you get the value as shown below

Solution

The IQR describes the middle 50% of values when ordered from lowest to highest. To find the interquartile range (IQR), first find the median (middle value) of the lower and upper half of the data. These values are quartile 1 (Q1) and quartile 3 (Q3).

So to achieve the desired result, we need to create a box plot. Place Sales measure on rows shelf and uncheck the option to disaggregate the values from following Menu Option,  Analysis -> aggregate measures.

Now place the measure and dimensions as shown below

Solution

Join Orders and Returns Table

Create a calculation to get the Sales Return Value for Customer

Create a calculated field to get the % of sales return against the total sales of customer

Put the measures of Sales, Sales Return and SR % on Text option in marks card and place customer on rows shelf

Now put filter to get the desired value

You end up with the total 34 such customers who have returned less than or equal to 20 % of their sales.

Solution

First solution by using the method of histogram.

Click on Age measure and select the option of Histogram from the Show Me option

It will plot the histogram as per default age bin size. Since requirement is to get the value of Gold medals with age bracket from 25-29.

Click on newly created dimension of Age Bin and edit the setting to make the age bins of size 5.

Now place the measure of Gold medals in Rows shelf and apply the filter country to Australia to get the desired value

Alternatively this question can be solved by simple way as shown below

Solution

The catch in this question is to identify the team who has won the toss has also won the match. We can get it by making a calculation field where we compare the fields winner and toss winner.

Now place the fields as shown in the image below

Now make a table calculation and user percent of total to get the percentages

Data looks like this

Solution

Select the Product Name dimension and create a set for Top 10 Products by Sales.

Next select the Product Name dimension and create another set for Bottom 10 Products by Profit.

Now select both sets created in last steps and create a combined set with an option to show only the shared members.

Now place the fields as shown in the figure below.

You must put the orders date in the filter and set it to context so that top and bottom filters get the values as per the selected year in the context.

Solution

Go to data source and split customer name to have first names and last name in separate columns.

Tableau will automatically split the customer names and will create two new field names as split 1 and split 2

Create calculation to get customers having first name ending letter

Create calculation to get customers having Last name starting letter

Place both calculated field on rows shelf and place sales in text as shown below

Now use the quick table calculation Percent of Total to find the share as shown below

Solution

Make a join of Orders and Returns Table, Put Region and Orders Date in filter pane to select East Region and year as 2011.

Put profit in Text option in marks card to reflect the Profit for East Region for year 2011.

Now select Status field coming from Returns table and place in Filter pane. It will show two values ‘Returned and NULL’. Keep only NULL option as selected to exclude the returned items to reach to desired value.

Solution

Put currency in the filers and select Binance-Coin currency, put year on columns shelf and ‘close’ measure on Text and select the average as shown below

Select the close measure in Marks card and select the quick table calculation Moving average

Requirement mentioned in the question is to find the moving average for average closing price for 4 years before 2018.

It means we have to exclude 2018 and get the moving average for previous 4 years i.e 2014, 2015, 2016 and 2017.

So following options should be entered for moving average table calculation. Click on Edit table calculation as shown below

It will show the moving average for year 2018 as shown below

Solution

Create a calculation field named as Shipment Days

Place the fields on rows and shelf columns as shown

Clearly seen there are 3 product categories which have average value less than 2.

Solution

First Put athlete dimension on columns shelf and Total medals on Rows shelf.

Secondly put Total Medals on filter pane and select the range of values option to select only those athletes who won medals from 7 to 15.

Sort the data in descending order

Since our requirement is to find the medals needed to be in 95th percentile so we have to use the distribution band from analytics pane at Table level as shown below

Once you place the distribution band on Table option, It will show the minimum goals needed to be at 95th percentile.

Solution
Background image needs to be mapped to datasource to reflect data point on the image.

Solution
You can add a reference line at a constant or computed value on the axis. Computed values can be based on a specified field. You can also include confidence intervals with a reference line.

Solution
INT Function Casts its argument as an integer. For expressions, this function truncates results to the closest integer toward zero.

Solution
Put a filter of Product ‘3M Organizer Strips

Put Order Date on Columns shelf, Product Name and Product Category on rows shelf

Now put Sales on Text on Marks card

Now Right Click on Sales in the Marks Card and select the Running Total Option to show the aggregate sales (running total) for every year

It show the following result and its clear that aggregate sales crossed 440 in year 2012

Solution 3
Put Sales on Columns and Customer Segment on Rows shelf

Put Ship Mode on Rows Shelf besides Customer Segment

Right Click on Sales Axis and click on Add Reference Line Option

Select the options as shown below

Pane will show the following view

From the following view, it’s obvious that average sales of corporate segment is higher.

Solution
Select Customer Name field and click on Set Option as shown below

Select the top option as shown below and click ok, it will create a set with name ‘Top 10 Customers By Profit’

Put Order Date field on Filters shelf

Select the year 2012

Put this filter to context

Put Set ‘Top 10 Customers By Profit’ on Rows shelf

Create calculated field ‘Profit Ratio’

Put Profit Ratio field on Text in the Marks card. It show that For Top 10 Customers Percentage value is 40.64 %.

Solution

Select the Country Field and click on symbol ABC

Select the geographic role to Country/ Region

As soon as you correct the geographic role, it will reflect the countries and values

Solution

Three date part components that can be used with DATEDIFF Function are as follows ‘day’,’month’,’year’. So the correct syntax is DATEDIFF('day',[Order Date],[Ship Date]).

Solution
FIXED is a reserve word used for LOD (Level of Detail). By Using FIXED, system gets instructions to groups data based on the dimension being used in FIXED clause.
Using {FIXED Region: Sum (Sales)} statement, Tableau will club the Sales on Region hence showing the sales per region.

Solution
Put customer segment on rows shelf

Put Product Category on Filter and select ‘Furniture’ as shown below

Put Order Date on Filter and select 2012

Now Put Sales on Text in the Marks Card

Now select Sales in the Marks Card, Right Click and select the Option ‘Percentage of Total’

It shows that Percentage of customer segment ‘Corporate’ for selected parameters in 38.20 %

Solution
Dashboard opens up like this

From the customer segment legend, we can identify which color show the corporate segment. Its Orange color.

Now we can click on corporate segment point for year 2010

It filters out the sheet for Product category sales to reflect the sales for ‘Office Supplies’. Upon click, it reflects the answer in the ‘Product Category wise Sales’ sheet as following screenshot show

Solution
When you use a sheet on dashboard, Delete option disappears for that sheet and becomes available only when you detach that sheet from the dashboard.
Sheets used on dashboard can be hided and there is no warning no hiding the sheets.

Solution
When we are using cubes for a multi-dimensional databases, We cannot make extracts in Tableau.

Solution
PDF Connector in Tableau extracts data in Tables that can be used for building visualizations.

Solution 2
Put Product Sub Category on Rows and sum of Profit on Columns

Create calculated field with following formula to get 30 percent of sales

Put filter of Region and select East

Put newly created calculated field ‘Target Profit Margin’ field on Detail in the Marks card

The view looks like this now

Put right click on axis and select the Add reference line option

Select the following option

From the view it is clear that ‘Binders and Binder Accessories’ and ‘Envelopes’ have crossed the target profit margin.

Solution 5
Tableau forecast is based on exponential smoothing, There is no way this computation mechanism. This is default computation method in Tableau.