DP22 - Using Beast Mode to Build Data Storytelling - Sales Pacing Graph
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:
**Did this solve your problem? Accept it as a solution!**
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 754 Beast Mode
- 61 App Studio
- 41 Variables
- 693 Automate
- 178 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive