DP22 - Using Beast Mode to Build Data Storytelling - Sales Pacing Graph

GrantSmith

This post is part of a series of beast modes I presented on during my “Using Beast Mode to Build Data Storytelling” Domopalooza 2022 session. If you find this post helpful please make sure to give it a like or an awesome below.

Use Case:

We needed a way to display and estimate in real time how well our sales were pacing for the current month to allow stakeholders to quickly make informed decisions. To do this we created a sales pacing graph to determine the current month’s running total and where sales are projected to be at the end of the month.

Beast Modes:

Running Total

SUM(SUM(`Daily Sales`)) OVER (PARTITION BY LAST_DAY(`Order Date`) ORDER BY `Order Date`)

Run Rate:

SUM(SUM(`Daily Sales`)) OVER (PARTITION BY LAST_DAY(`Order Date`) ORDER BY `Order Date`) / DAYOFMONTH(`Order Date`) * DAYOFMONTH(LAST_DAY(`Order Date`))

Beast Mode Breakdown:

Running Total

SUM(SUM(`Daily Sales`)) OVER (PARTITION BY LAST_DAY(`Order Date`) ORDER BY `Order Date`)

OVER denotes a window function. Window functions in beast modes are a feature switch. Talk with your CSM to get this enabled.

Window functions allow us to calculate an aggregation across the partition or entire dataset and return that value as a single value within each row.

PARTITION BY LAST_DAY(`Order Date`)

PARTITION BY Defines the bucket we want to aggregate over

Can exclude to aggregate over the entire dataset.

LAST_DAY returns the last day in a month. This is a way to group by each month. It’s an alternative to (PARTITION BY YEAR(`Order Date`), MONTH(`Order Date`))

ORDER BY `Order Date`

Defines the order in which to calculate the running total.

If excluded it will be the grand total within your partition.

SUM(SUM(`Daily Sales`))

Normal SQL is a single aggregation however Domo can potentially do aggregation twice. First when the data is initially loaded into the card. Another time when you have selected aggregation on another field on the card.

Because a window function returns a single value for each row we need to tell Domo how to aggregate the data (again) when the card does aggregation.

Run Rate

SUM(SUM(`Daily Sales`)) OVER (PARTITION BY LAST_DAY(`Order Date`) ORDER BY `Order Date`) / DAYOFMONTH(`Order Date`) * DAYOFMONTH(LAST_DAY(`Order Date`))

SUM(SUM(`Daily Sales`)) OVER (PARTITION BY LAST_DAY(`Order Date`) ORDER BY `Order Date`)

This is the Running Total beast mode from above. We want to get the month's total at the date given.

/ DAYOFMONTH(`Order Date`)

Divide by the day number of the order date (DAYOFMONTH) to get the average amount per day. In other words the number of days that have elapsed in the current month as of the Order Date

* DAYOFMONTH(LAST_DAY(`Order Date`))

LAST_DAY returns the last date in the given month. DAYOFMONTH will tell us the day number of the last day of that month. In other words, the number of days in the given month. Multiplying our average we just calculated will give us an estimate for the end of the month as of the Order Date value.

Final Result: