2024 Week 30 | Sigma: Can you show partial or whole months?

Sigma, Workout Wednesday / By

Introduction

This week’s challenge was inspired by a lively discussion I was a part of, covering what date calculations a data analyst should or should not know. We’ve already covered several common ones, such as the current time period vs the prior time period. So this week, I wanted to add another to the list: identifying partial months!

Have fun!
-Katrina

Need access to Sigma?

Click Here to Sign Up
Link to WOW Sigma Instance
Youtube
Click here to Watch: How to Register & Get Started with Sigma WoW

Note: You will only have view access to WOW Workbooks, Folders, and Workspaces, not edit access. Please create your WOW Workbooks under “My Documents.” We suggest creating a folder to organize all your workbooks. 

Click to Open an Interactive Workbook

Requirements

  • Add a data table element from the Plugs Electronic Folder
    • Sigma Sample Database > Retail > Plugs Electronic > Plugs Electronics Hands On Lab
  • Create a Switch to control if the data contains the current month or not
  • Create a KPI showing Revenue by week compared to this week last month.
  • Include a dynamic title based on the date range
  • Include an indicator of the start of the month on the trend line
  • Match other formatting
  • Create a bar and line chart showing the Revenue and Cost of Goods by Week Number and Region for the last 4 months and potentially the current month
  • Have the lines be colored differently:
    • Here are the colors I used; you can choose different ones
    • Revenue: DDDDDD
    • Current Month COGs: E91E63
    • Other Months COGs: 388E3C
  • Match other formatting
  • Add challenge title

  • Revenue = Sum([Price] * [Quantity])
  • COGS = Sum([Cost] * [Quantity])

Dataset

Sigma Sample Database > Retail > Plugs Electronic > Plugs Electronics Hands On Lab

Share

After you finish your workout, share on LinkedIn, Sigma’s Community page, (or Twitter) using the hashtags #WOW2024 and #SigmaComputing, and tag Ashley Bennett, Eric Heidbreder, Katrina Menne, and Michal Shaffer!

Create an interactive, sharable version of your solution here.

Also, make sure to fill out the Submission Tracker so that we can count you as a participant this week to track our participation throughout the year.

Solution

  1. Create a “Show partial or whole month” switch control element.
  2. In the parent table element, create a formula for if the date is within the selected time frame.
    1. If([New-Control], True, (InDateRange([Date], “current”, “month”)))
  3. In the same table, create a week of date calculation
    1. DatePart(“week”, [Date])
  4. For the KPI
    1. Create a child KPI off of the parent table element
    2. Put Revenue as the value
    3. Week of Date as the timeline
    4. Comparison is “This week last month”
    5. Add a reference line on the x-axis with the custom formula of 
      1. If([New-Control], DateTrunc(“month”, Today()))
    6. Change the title to be dynamic with the Min & Max Date formatted to Month and year 
  5. For Combo Chart
    1. Add a combo chart with the Week of Date as the X-Axis
    2. On the Y-Axis add three calculations:
      1. Revenue (either the Metric or sum([Revenue])
      2. Current Month COG
        1. If(InDateRange([PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA/Date], “current”, “month”), [Metrics/COGS])
      3. Other Months COG
        1. If(Not (InDateRange([PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA/Date], “current”, “month”)), [Metrics/COGS])
    3. Display Revenue as bars and COGS as lines
    4. Color each measure separately
    5. Trellis the Combo Chart by Store Region
Scroll to Top