Establishing quarterly goals is an effective method to enhance focus and accountability. However, monitoring weekly progress toward these goals can be complex. The varying number of weeks in a quarter and occasional partial weeks at the end can make calculating progress feel more like managing calendars than concentrating on outcomes.
Fortunately, Sigma streamlines this process. Utilizing its user-friendly interface and robust calculations, you can develop a dashboard that tracks your weekly contributions and accommodates those challenging partial weeks. In this blog, we will guide you on calculating weekly progress towards quarterly goals using Sigma.
Why not use DateTruncate?
Like most analytics platforms, Sigma has DateTrunc to adjust a date or datetime value to match the starting point of a specified time unit, such as a year, quarter, month, or week. This simplifies the date by removing the finer details (like hours, minutes, or days) beyond the selected period, aligning the value to its earliest possible moment in that unit.
However, if we use DateTrunc to calculate a weekly total, some weeks cross multiple quarters. For example, in 2021, the first quarter ends on a Wednesday resulting in the start of the week being 2021-03-28 for both the last week of Q1 & the first week of Q2.
Step 1: Group by Quarter
First, we need to group our data by Quarter. Be sure to include other dimensions required to group your data accurately. In our example, we'll track weekly progress toward quarterly sales targets by Region.
Note: Sigma labels the quarter as the first month of the quarter.
Step 2: Create a "Fake" Week Field
Instead of using DateTrunc for the week, we will manufacture the start date of a quarter if the week is not in the same quarter. We only need to manufacture dates for new quarters, because if the first or second month ends in the middle of the quarter that's fine! It's still in the same quarter.
Here is the formula to manufacture a "Fake" Week:
If([Quarter] = DateTrunc("quarter", DateTrunc("week", [Day])), DateTrunc("week", [Day]), MakeDate(Year([Quarter]), Month([Quarter]), 1))
How does this formula work in plain English?
First, we ask if we truncate this quarter like normal would it be in the same quarter as the quarter it is grouped to?
If([Quarter] = DateTrunc("quarter", DateTrunc("week", [Day])),
If it is the same quarter, then truncate the week like normal.
DateTrunc("week", [Day])
If it is not the same quarter, then make a new date using the year and first month of the grouped quarter and the number 1 for the day since its going to be the first of the next Quarter.
Step 3: Group by the Fake Week Field
Now that we have a fake week label, we can calculate the weekly sales total. Add a new grouping level for the fake week and sum the sales.
Step 4: Add the quarterly quota
Add your quota (or whichever metric you are calculating progress toward) at the quarterly level. I used a Lookup, but there are several ways to add this data.
Lookup([Quota/Quota], [Store Region], [Quota/Store Region], [Quarter], [Quota/Quarter])
Step 5: Calculate Weekly Progress
Lastly, we can add our weekly progress percentage. Divide the CumulativeSum, which automatically creates a running sum of sales, and then divide it by the Quarterly Quota.
CumulativeSum([Sum of Sales]) / [Quota (Quota)]
Note: Cumulative Sum is a window calculation dependent on the row order. If there is concern about the row order changing, sort by the week field.
Tracking progress toward quarterly goals can get messy when weeks overlap quarters, but with Sigma, you’re not stuck trying to make sense of misaligned dates. By creating a "fake" week field and grouping data thoughtfully, you can calculate weekly progress with accuracy and ease—even when partial weeks complicate things.
Sigma’s flexibility allows you to handle the quirks of time-based reporting without compromising clarity. With these steps, you’re equipped to build dashboards that highlight progress and keep stakeholders informed.