2024 Week 26 | Sigma: Can you calculate RunRates?

Adding Data

[If you have completed week 18 or 22, you can use your existing workbook and skip the next two points.]

• Add data table element from TASTY_BYTES_FOOD_TRUCKS schema
  • Sigma Sample Database > Retail > TASTY_BYTES_FOOD_TRUCKS
    • TRUCKS – has truck information
    • FRANCHISE – has information on franchise owners
• In the FRANCHISE table, create a new column with the full name of the franchise owners.
• Use a lookup to get the franchise owner full name into the TRUCKS table.
• Add data table element
  • FCT_DAILY_ORDER_PROFIT – has daily order information
• Use a lookup to get the franchise owner full name in to the FCT_DAILY_ORDER_PROFIT table from the TRUCKS table.
• Group the FCT_DAILY_ORDER_PROFIT table:
  • First grouping level – Franchise Owner Full Name
  • Second grouping level -Truck Brand Name, Truck ID
  • Third grouping level –  Order Month
    • Calculations
    • Total Sales Actual (Dollar Value)
  • Fourth grouping level – Order Date
    • Calculations
    • Total Sales Actual – Day (Dollar Value)
• Add a page control for order date (set to last 6 months)
• Add a page control for the Franchise owner (no multiple selection, set to any franchise owner).
• Add a page control for the Truck Brand Name (multiple selection allowed)

Run Rates

• Add helper columns to calculate the classic run rate.
  • In the Order Month grouping level:
  • isCurrentMonth – Month is the Current Month (True or False) (Hint: Use DateTrunc() and Today())
  • daysInMonth – Total days in the Month (number) (Hint: Use Day() and  (EndOfMonth())
  • maxDayInMonth – Day of the last order date that has data (number)
• Calculate Classic Run Rate if the Order month is the current month:
  • Run Rate Classic: Divide the [actual total sales] by the days in the month that have passed (maxDayInMonth), and then multiply that daily number by the total days in the month.
• Add helper columns to calculate the run rate using sales moving average
  • In the Order Date grouping level:
  • 7 Day Moving Average of Total Sales (Dollar Value) (Hint: Use movingavg())
  • In the Order Month grouping level:
  • Current Moving Avg – Get the 7 Day Moving Average for the last day of data in the Month (Hint: use an if statement to check for the max day of the month)
• Calculate the RunRate using the Moving Average if the month is the current month.
  • Run Rate MovingAvg- Use the moving average as the daily sales amount for the remaining days in the month, and add that to the [sales total actual for the month.
• Add helper columns to calculate the run rate using linear regression
  • In the Order Month grouping level:
  • X – a column with the rownumber() function
  • In the Truck ID Grouping Level:
  • Slope: If([isCurrentMonth] = False, Corr([Total Sales Actual], [X]) * Stddev([Total Sales Actual]) / Stddev([X]))
  • Intercept: If([isCurrentMonth] = False, Avg([Total Sales Actual]) – [Slope] * Avg([X]))
  •        Note: we don’t want to include the current month data in the regression formula because it is not a full months of data and will skew the line downward.
• Calculate the RunRate using Linear Regression if the month is the current month.
  • In the Order Month grouping level: Run Rate LinearReg: [Slope] * [X] + [Intercept]

Controls, Cleanup, Additional Metrics, and Formatting

• Add a segmented control with the following options: Actual Sales Total, RunRate Classic, RunRate MovingAvg, RunRate LinearReg
• Add a new column called Total Sales that uses the appropriate Run Rate or Actual sales based on the segmented control, as well as whether or not the order month is the current month. (Hint: Use an if() for current month vs not current month, and a switch() for the control selection).
• Hide all helper columns and run-rate columns so you are just left with the Total Sales by month.
• In the table components section of the element formatting, hide collapsed columns.
• Add a column [Prev Month Sales] in the Order Month grouping level to show the previous month’s total sales. (Hint: use lag()).
• Add a column to show the Month over Month dollar change from the previous month to the current month. 
• Add a column to show the Month over Month percentage increase or decrease from the previous month to the current month. 
• Add a red to green color scale conditional formatting on the % MoM change column. Customize the domain to show positive numbers as green, and negative numbers as red.
• Add a background color to the Total Sales column.
• Create a line chart as a child element of your main table, displaying the order month in the X axis, the Total Sales in the Y axis, and the Truck Brand Name in the colors. Add data labels on all, and show points.
• Create a KPI visualization as a child element of your main table, displaying the sum of the Total Sales for the current month, with the sum of the previous month’s total sales as the comparison. (Hint: use sumif() for both columns). The comparison should show the percentage change, with higher being better.
• Format your table and visualizations to your own preference. Arrange the elements on your page for easy readability.
• Don’t forget to share your solution!