TableauBI

Level of Detail (LOD) expressions in Tableau allow you to compute aggregations at different levels of granularity than the visualization level. Here are 10 different scenarios with calculations using LOD expressions:

1. Total Sales by Customer, Regardless of Filter: {FIXED [Customer Name] : SUM([Sales])} This calculation ensures that the total sales for each customer are computed regardless of any filters applied to the visualization.
2. Percentage of Total Sales by Product Category: {INCLUDE [Product Category] : SUM([Sales])} / {TOTAL(SUM([Sales]))} This calculation computes the percentage of total sales contributed by each product category.
3. Running Total Sales by Date: {FIXED [Order Date] : RUNNING_SUM(SUM([Sales]))} This calculation provides a running total of sales over time, regardless of the granularity of the visualization.
4. Average Sales per Customer, Considering Only Selected Products: {EXCLUDE [Product Name] : AVG({INCLUDE [Customer Name] : SUM([Sales])})} This calculation computes the average sales per customer, considering only the selected products.
5. Top N Customers by Sales: {FIXED [Customer Name] : RANK(SUM([Sales]), 'desc')} This calculation ranks customers based on their total sales and allows you to filter for the top N customers.
6. Year-over-Year Growth Rate of Sales: (SUM({FIXED YEAR([Order Date]) : SUM([Sales])}) - SUM({FIXED YEAR([Order Date])-1 : SUM([Sales])})) / SUM({FIXED YEAR([Order Date])-1 : SUM([Sales])}) This calculation computes the year-over-year growth rate of sales by comparing the sum of sales for the current year with the sum of sales for the previous year.
7. Rolling Average Sales for the Last N Months: {FIXED DATETRUNC('month', [Order Date]) : WINDOW_AVG(SUM([Sales]), -N, 0)} This calculation calculates the rolling average of sales for the last N months.
8. Customer Retention Rate: COUNTD({FIXED [Customer Name] : MIN(IF YEAR([Order Date]) = YEAR(TODAY()) THEN [Order Date] END)}) / COUNTD({FIXED [Customer Name] : MIN([Order Date])}) This calculation computes the percentage of customers retained from the previous year to the current year.
9. Profit Margin by Product: SUM([Profit]) / SUM([Sales]) This simple calculation computes the profit margin for each product.
10. Market Share of Products within Each Category: {INCLUDE [Product Category], [Product Name] : SUM([Sales])} / {INCLUDE [Product Category] : SUM([Sales])}

More Complex LOD calculations below:

1. Weighted Average Sales by Region, Considering Discounts: {FIXED [Region] : SUM([Sales] * [Discount])} / {FIXED [Region] : SUM([Quantity] * (1 - [Discount]))} This calculation computes the weighted average sales by region, considering the discounts applied to each transaction.
2. Moving Average Sales for the Last N Days, Excluding Weekends: {EXCLUDE DATEPART('weekday', [Order Date]) : WINDOW_AVG(SUM([Sales]), -N, 0)} This calculation calculates the moving average of sales for the last N days, excluding weekends from the calculation.
3. Customer Lifetime Value (CLV): {FIXED [Customer ID] : SUM([Sales])} / {COUNTD([Order ID])} This calculation computes the average sales per order for each customer, providing insights into customer lifetime value.
4. Churn Rate: 1 - ({COUNTD(IF DATEDIFF('day', MAX([Order Date]), TODAY()) <= 30 THEN [Customer ID] END)} / {COUNTD([Customer ID])}) This calculation computes the churn rate by comparing the count of active customers in the last 30 days to the total count of customers.
5. Sales Rank within Each Product Category: {INCLUDE [Product Category], [Product Name] : RANK(SUM([Sales]), 'desc')} This calculation ranks products within each product category based on their sales.
6. Cohort Analysis – Average Sales Retention Rate: WINDOW_AVG(COUNTD(IF DATEDIFF('month', [First Purchase Date], [Order Date]) = 0 THEN [Customer ID] END), -1, 0) / COUNTD({FIXED [First Purchase Date] : [Customer ID]}) This calculation computes the average sales retention rate across cohorts based on the month of the first purchase.
7. Pareto Analysis – Cumulative Sales Percentage: RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales])) This calculation computes the cumulative percentage of total sales, allowing you to identify the top contributing segments.
8. Time-based Aggregation Adjusted for Holidays: {EXCLUDE [Holiday Date] : SUM([Sales])} / {EXCLUDE [Holiday Date] : COUNTD(DATETRUNC('day', [Order Date]))} This calculation adjusts sales aggregation by excluding holidays to provide a more accurate view of sales performance over time.
9. Rolling Median Sales for the Last N Months: {FIXED DATETRUNC('month', [Order Date]) : WINDOW_MEDIAN(SUM([Sales]), -N, 0)} This calculation computes the rolling median of sales for the last N months, providing insights into sales trends while minimizing the impact of outliers.
10. Customer Basket Analysis – Average Number of Items per Transaction: {FIXED [Order ID] : AVG({FIXED [Order ID] : COUNTD([Product ID])})} This calculation computes the average number of items per transaction, allowing you to understand customer purchasing behavior.

Tableau Parameters use cases:

Tableau parameters are values that can be used as inputs in calculations, filters, or other parts of a Tableau worksheet. There are several types of parameters in Tableau, including:

1. Numeric parameter: a numerical value that can be used in calculations, such as a discount rate or a sales target.
2. Date parameter: a date value that can be used in filters, calculations, or as an input for a date calculation.
3. String parameter: a text value that can be used as a filter, a reference in a calculation, or to dynamically change the title of a view.
4. Boolean parameter: a true/false value that can be used as a filter or in calculations to control the behavior of a view.
5. List parameter: a list of values from which the user can choose one or more items to be used in a filter, calculation, or as a parameter for a calculated field.

1. Filter by Product Category (String Parameter):
   • Create a string parameter named “Product Category” by going to “Parameters” > “Create Parameter” and choosing “String” data type. Define the list of values representing different product categories.
   • Create a calculated field named “Filter by Product Category” with the following formula:sqlCopy codeIF [Product Category] = [Parameters].[Product Category] OR [Parameters].[Product Category] = "All" THEN "Include" ELSE "Exclude" END
   • Drag this calculated field to the Filter shelf and select “Include” to filter data by the selected product category.
2. Setting Thresholds (Number Parameter):
   • Create a number parameter named “Sales Target” by going to “Parameters” > “Create Parameter” and choosing “Float” data type. Define the range of values representing sales targets.
   • Create a calculated field named “Sales Target Met” with the following formula:sqlCopy codeIF SUM([Sales]) >= [Sales Target] THEN "Met" ELSE "Not Met" END
   • Use this calculated field for analysis, such as creating a bar chart showing sales performance against the sales target.
3. Defining Ranges (Number Parameter):
   • Create a number parameter named “Age Range” by going to “Parameters” > “Create Parameter” and choosing “Integer” data type. Define the range of values representing age ranges.
   • Create a calculated field named “Age Group” with the following formula:sqlCopy codeIF [Age] <= [Parameters].[Age Range] THEN "Under " + STR([Parameters].[Age Range]) ELSE "Over " + STR([Parameters].[Age Range]) END
   • Use this calculated field for grouping and analyzing data based on age ranges.
4. Calculations (Number Parameter):
   • Create a number parameter named “Discount Rate” by going to “Parameters” > “Create Parameter” and choosing “Float” data type. Define the range of values representing discount rates.
   • Create a calculated field named “Discounted Price” with the following formula:sqlCopy code[Price] * (1 - [Parameters].[Discount Rate])
   • Use this calculated field to calculate discounted prices based on the selected discount rate parameter.

Tableau Lookup functions and use cases in detail

1. Previous Period Sales Comparison: Project Requirement: Compare current period sales with the previous period. Calculation: Use a table calculation such as LOOKUP(SUM([Sales]), -1) to retrieve the sales value from the previous period. Then, compute the difference or percentage change between the current and previous period sales.
2. Rolling Average Sales: Project Requirement: Calculate the rolling average of sales for the last N periods. Calculation: Use a table calculation such as RUNNING_AVG(SUM([Sales]), -N, 0) to compute the rolling average of sales over the last N periods.
3. Year-over-Year Sales Growth: Project Requirement: Analyze year-over-year sales growth. Calculation: Use an LOD expression such as {FIXED YEAR([Order Date]): SUM([Sales])} to calculate the total sales for each year. Then, compute the year-over-year growth rate using a table calculation or LOD expression.
4. Top N Products by Sales: Project Requirement: Identify the top N products by sales. Calculation: Use a table calculation such as RANK(SUM([Sales])) <= N to rank products based on sales and filter for the top N ranked products.
5. Moving Average of Profit Margin: Project Requirement: Compute the moving average of profit margin over a specified period. Calculation: Use a table calculation such as WINDOW_AVG([Profit Margin], -N, 0) to calculate the moving average of profit margin over the last N periods.
6. Lookup Customer Lifetime Value (CLV): Project Requirement: Retrieve the customer lifetime value (CLV) for each customer. Calculation: Use an LOD expression such as {FIXED [Customer ID]: SUM([Sales])} / {COUNTD([Order ID])} to calculate the average sales per order for each customer, representing CLV.
7. Next Period Forecasting: Project Requirement: Forecast sales for the next period. Calculation: Use a predictive model or time series forecasting algorithm to predict future sales based on historical data. This may involve using techniques such as exponential smoothing, ARIMA models, or machine learning algorithms.
8. Lag Analysis: Project Requirement: Analyze the lag effect of marketing campaigns on sales. Calculation: Use a table calculation such as LOOKUP(SUM([Sales]), 1) to retrieve the sales value from the previous period and compare it with the current period to analyze the lag effect.
9. Market Basket Analysis: Project Requirement: Identify frequently co-occurring products in customer transactions. Calculation: Use data blending or LOD expressions to analyze transaction data and identify pairs or groups of products frequently purchased together.
10. Customer Churn Analysis: Project Requirement: Predict customer churn based on historical behavior. Calculation: Use predictive modeling techniques such as logistic regression, decision trees, or random forests to build a churn prediction model based on customer attributes and behavioral data.

USER FUNCTIONS IN TABLEAU

In Tableau, user functions are a set of functions that allow you to interact with user inputs and parameters. These functions are typically used in calculated fields to dynamically adjust the behavior of visualizations based on user selections or inputs. Some common user functions in Tableau include:

1. USERNAME():
   • Returns the username of the current user.
2. ISMEMBEROF(“group_name”):
   • Checks if the current user is a member of a specified Tableau Server group.
3. ISNULL(expression):
   • Returns true if the expression evaluates to NULL, and false otherwise.
4. USERDOMAIN():
   • Returns the domain name of the current user.
5. ISLOGGEDIN():
   • Returns true if the user is logged in to Tableau Server, and false otherwise.
6. ISUSERNAME(“username”):
   • Checks if the current user’s username matches the specified username.
7. ISFULLNAME(“full_name”):
   • Checks if the current user’s full name matches the specified full name.
8. ISMEMBEROF(“group_name”):
   • Checks if the current user is a member of the specified Tableau Server group.

The ISMEMBEROF() function in Tableau is typically used in Tableau Server or Tableau Online environments to check if the current user is a member of a specified group. Here’s an example scenario and how you can use ISMEMBEROF():

Scenario: Suppose you have a Tableau dashboard that contains sensitive information, and you want to restrict access to users who belong to a specific group called “Managers.”

Example Usage:

1. Create a calculated field named “Is Manager”: ISMEMBEROF("Managers")
2. Use the “Is Manager” calculated field to control access to the dashboard:
   • In the dashboard, create a filter using the “Is Manager” calculated field.
   • Set the filter to show only users where “Is Manager” is true.
   • Users who are members of the “Managers” group will see the filtered dashboard, while others will not.