Analytics-Con-301 Reviews Questions

To compute the required calculations and update the worksheet in Tableau Desktop, follow these steps:

Compute Average Sales by Segment for Each Category and Year:

Open the CC workbook and navigate to the Categorical Sales worksheet.

Drag the 'Sales' field to the Rows shelf if it's not already there.

Drag the 'Segment' field to the Rows shelf as well, placing it next to 'Category' and 'Year'.

Right-click on the 'Sales' field in the Rows shelf and select 'Quick Table Calculation' > 'Average'. This will compute the average sales for each segment within each category and year.

Create a Calculation for Year-over-Year Percentage Change:

Right-click in the data pane and select 'Create Calculated Field'.

Name the calculated field something descriptive, e.g., "YoY Sales Change".

Enter the formula to calculate the year-over-year percentage change:

(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

Click 'OK' to save the calculated field.

Replace the Original Measure with the Year-over-Year Percentage Change in the Crosstab:

Remove the original 'Sales' measure from the view by dragging it off the Rows shelf.

Drag the newly created "YoY Sales Change" calculated field to the Rows shelf where the 'Sales' field was originally.

Format the "YoY Sales Change" field to display as a percentage. Right-click on the field in the Rows shelf, select 'Format', and adjust the number format to percentage.

Save Your Changes:

From the File menu, click 'Save' to ensure all your changes are stored.

To add a line showing the cumulative percentage of sales contributed by each product to the incremental sales in the Incremental worksheet of your Tableau Desktop, follow these detailed steps:

Open the CC Workbook and Access the Worksheet:

From the desktop, double-click on the CC workbook to open it in Tableau Desktop.

Navigate to the Incremental worksheet by clicking on its tab at the bottom of the window.

Calculate Cumulative Sales Percentage:

Create a new calculated field to compute the cumulative percentage of sales. Right-click in the Data pane and select 'Create Calculated Field'.

Name this field "Cumulative Sales Percentage".

Enter the following formula to calculate the running sum of sales as a percentage of the total sales:

(RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales])) [Sales]))

Click 'OK' to save the calculated field.

Add the Cumulative Sales Percentage Line to the Chart:

Drag the "Cumulative Sales Percentage" field to the Rows shelf, placing it next to the existing Sales measure.

Ensure that the cumulative line appears as a continuous line. Right-click on the "Cumulative Sales Percentage" field on the Rows shelf, select 'Change Chart Type', and choose 'Line'.

Adjust the axis to synchronize or dual-axis if necessary. Right-click on the axis of the "Cumulative Sales Percentage" and select 'Synchronize Axis' if it's on a dual-axis setup.

Format the Cumulative Sales Percentage Line:

Click on the "Cumulative Sales Percentage" line in the visualization.

Navigate to the 'Format' pane to adjust the line style, thickness, and color to make it distinct from other data in the chart.

Save Your Changes:

From the File menu, click 'Save' to ensure all your changes are stored.

Comprehensive and Detailed Explanation From Exact Extract:

To combine Excel and SQL Server data in the same logical data model, Tableau offers two supported capabilities:

Relationships

Recommended modern method for combining tables from multiple sources.

Supports cross-database relationships between Excel and SQL Server.

Maintains separate physical layers but integrates data at query time.

Cross-Database Joins

Allows joining data from different databases in the physical layer.

Fully supported for Excel + MS SQL Server.

Useful when granular row-level merging is needed.

Why the other options are incorrect:

C. Data Blending

Legacy feature, used only when no direct combination is possible.

Tableau recommends relationships instead.

Produces separate queries and may lose row-level detail.

D. Union

Requires tables to have equivalent structure.

Cannot union Excel with SQL Server unless identical column structure exists.

Not appropriate for most mixed-source reporting.

Therefore, the correct techniques are Relationships and Cross-Database Joins.

Tableau data modeling documentation recommending Relationships for multi-source modeling.

Cross-database join support list including Excel + SQL Server.

Comprehensive and Detailed Explanation From Exact Extract:

Tableau allows fiscal calendars to be defined at the data source level, affecting how all date fields behave across the workbook.

According to Tableau documentation:

Fiscal calendars must be set using Data Source Date Properties.

Once set, this becomes a default property for all date fields unless overridden.

This allows charts, hierarchies, and date parts to automatically follow the fiscal year starting in February.

Correct procedure:

Go to Data Source.

Open Date Properties.

Set Fiscal Year Start = February.

(Optional) Adjust Date Field Default Properties.

This ensures all charts and date hierarchies use the fiscal calendar automatically.

Why the other options are incorrect:

A. Right-click on axis → Use Fiscal Calendar

This option does not exist in Tableau.

B. Set Fiscal Year in Data Preview

Not supported; fiscal configuration isn’t made in the preview window.

C. Use FISCALYEAR() / FISCALQUARTER()

This is manual, requires custom fields, and does not configure Tableau’s built-in fiscal calendar system.

This is more work and not the correct method.

Only Option D configures the fiscal calendar globally and correctly.

Tableau Date Properties documentation specifying fiscal year settings.

Default Properties configuration for date fields.

Official guidance for implementing fiscal calendars.