Trend analysis in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Year-over-Year Sales Growth Using LOD Expression

You have a sales dataset with fields OrderDate and SalesAmount. You want to calculate the year-over-year sales growth percentage using a Level of Detail (LOD) expression in Tableau.

Which of the following LOD expressions correctly calculates the total sales for the previous year to use in the growth calculation?

A { FIXED YEAR([OrderDate]) - 1 : SUM([SalesAmount]) }

B { FIXED YEAR(DATEADD('year', -1, [OrderDate])) : SUM([SalesAmount]) }

C { FIXED DATEPART('year', DATEADD('year', -1, [OrderDate])) : SUM([SalesAmount]) }

D { FIXED YEAR([OrderDate]) : SUM([SalesAmount]) }

💡 Hint

Think about how to fix the year to the previous year using date functions inside the LOD.

Option C correctly fixes the calculation to the previous year by using DATEPART with DATEADD to shift the year back by one, then sums sales for that year.

❓ visualization

intermediate

Best Visualization for Showing Monthly Sales Trend with Forecast

You want to create a dashboard that shows monthly sales trends over the past two years and includes a forecast for the next 6 months.

Which visualization type in Tableau best fits this requirement?

A Line chart with a continuous date axis and built-in Tableau forecast enabled

B Bar chart grouped by month with color encoding for forecasted months

C Pie chart showing sales distribution by month with forecast slice

D Scatter plot with sales on Y-axis and month number on X-axis

💡 Hint

Consider which chart type best shows trends over time and supports forecasting.

Line charts with continuous date axes are ideal for showing trends over time and Tableau's built-in forecast feature works best with them.

🧠 Conceptual

advanced

Understanding Moving Average for Trend Smoothing

Why is a moving average commonly used in trend analysis dashboards?

A To replace missing data points with average values

B To remove seasonal fluctuations and highlight the underlying trend

C To increase the number of data points for better granularity

D To convert categorical data into numerical data

💡 Hint

Think about what moving averages do to noisy data.

Moving averages smooth out short-term fluctuations, making it easier to see the overall trend by reducing noise like seasonal effects.

❓ data_modeling

advanced

Designing Data Model for Multi-Year Trend Analysis

You need to build a data model in Tableau to analyze sales trends over multiple years with the ability to filter by product categories and regions.

Which data modeling approach is best to support fast and flexible trend analysis?

A Only a fact table with no dimension tables, using calculated fields for categories

B Single flat table with all sales, product, region, and date columns combined

C Separate tables for each year with no shared dimension tables

D Star schema with a fact sales table linked to dimension tables for date, product, and region

💡 Hint

Think about how dimension tables help with filtering and performance.

A star schema separates facts and dimensions, enabling efficient filtering and aggregation for trend analysis across multiple years.

🔧 Formula Fix

expert

Identify the Error in Trend Line Calculation

A Tableau user created a calculated field to compute a 3-month moving average of sales:

WINDOW_AVG(SUM([SalesAmount]), -2, 0)

However, the moving average does not appear correctly on the dashboard.

What is the most likely cause of the problem?

A The calculation does not specify the correct addressing and partitioning, causing incorrect windowing

B WINDOW_AVG cannot be used with SUM aggregation inside it

C The window frame should be from 0 to 2 instead of -2 to 0

D The calculation needs to use RUNNING_AVG instead of WINDOW_AVG

💡 Hint

Consider how Tableau computes table calculations and the importance of addressing.

Without properly setting addressing and partitioning, WINDOW_AVG may compute over wrong data points, causing incorrect moving averages.