Sample Beast Mode Calculations: Period-over-Period Transforms
Intro
This article lists a number of period-over-period calculations you can try out in Beast Mode. Many of these same calculations may also be possible using our Period-over-Period charting functionality.
Creating a Period-over-Period Graph
You can create a period-over-period graph in Domo using one Beast Mode calculation for the x-axis (date) and another Beast Mode calculation for the series. You can also create a Beast Mode calculation to limit the date ranges shown on the card.
Start creating your own period-over-period Beast Mode calculations using the samples provided below. The following examples show you how to create a year-over-year graph as a demonstration.
Step 1: Create the x-axis (date) calculation (REQUIRED)
In this step you project the dates from previous periods, such as years, onto the current period. In this way, data from different periods will appear on the same range on the x-axis. This Beast Mode calculation replaces the year in a date value with the current year.
STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE()), '-', DATE_FORMAT(`Date_Field`, '%m-%d') ), '%Y-%m-%d')
Step 2: Create the series (year) calculation (REQUIRED)
Next you create a series that represents each period’s data. This Beast Mode calculation lets you create a series that represents the data for each year.
YEAR(`Date_Field`)
Step 3: Create calculations for filtering (OPTIONAL)
If you want to see a time frame besides a full year (quarter-over-quarter, month-over-month, etc.), you can create a filter that allows you to get just the date range or period you’re interested in.
There are two different methods for creating Beast Mode calculations for filtering. The first method lets you create a filter for a specific date range. The second method lets you create a filter for a specified number of previous days, months, etc.
Specific Date Range
Use this Beast Mode calculation to create a filter for a specific date range.
CASE
WHEN CONCAT(YEAR(CURRENT_DATE()), '-', DATE_FORMAT(`Date_Field `, '%m-%d') ) >= '2015-07-01' AND CONCAT(YEAR(CURRENT_DATE()), '-', DATE_FORMAT(`Date_Field `, '%m-%d') ) <= '2015-11-30' THEN 'TRUE'
ELSE 'FALSE'
END
Last ‘X’ Days, Months, etc.
Use this Beast Mode calculation to create a filter for a specified number of periods back. Replace “3 MONTH” with whatever interval you’re interested in, e.g. “1 DAY,” “4 WEEK,” etc. Just use the number and the singular version of the unit.
CASE
WHEN STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE()), '-', DATE_FORMAT(`Date_Field `, '%m-%d') ), '%Y-%m-%d') >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH) THEN 'TRUE'
ELSE 'FALSE'
END
You can use variations of the above filters to limit the range in whatever way makes the most sense to you and your audience.
Calculating Week-over-Week Change
Use the following code to create a calculation that shows week-over-week change. The following example includes 4 weeks prior (aligned on days), and 52 weeks prior (aligned on days). Change the values to the time periods you want.
X Axis is Date - has to be "Common Date":
case
when DateDiff(AddDate(Current_Date(), -1), `MyDateColumn`) < 28 and DateDiff(Current_Date(), `MyDateColumn`) > 0 then Date_Format(`MyDateColumn`, '%b %e')
when DateDiff(AddDate(Current_Date(), -1), `MyDateColumn`) < (28 + 28) and DateDiff(Current_Date(), `MyDateColumn`) > 28 then Date_Format(AddDate(`MyDateColumn`,28), '%b %e')
when DateDiff(AddDate(Current_Date(), -1), `MyDateColumn`) < 28 + (52 * 7) and DateDiff(Current_Date(), `MyDateColumn`) > (52 * 7) then Date_Format(AddDate(`MyDateColumn`,52 * 7), '%b %e')
end
Filter to "Last 28 Days":
case
when DateDiff(AddDate(Current_Date(), -1), `MyDateColumn`) < 28 and DateDiff(Current_Date(), `MyDateColumn`) > 0 then 'Yes'
when DateDiff(AddDate(Current_Date(), -1), `MyDateColumn`) < (28 + 28) and DateDiff(Current_Date(), `MyDateColumn`) > 28 then 'Yes'
when DateDiff(AddDate(Current_Date(), -1), `MyDateColumn`) < 28 + (52 * 7) and DateDiff(Current_Date(), `MyDateColumn`) > (52 * 7) then 'Yes'
else 'No'
end
Pick your metric (in this example, "Visits"), then create a column called "Series":
case
when DateDiff(AddDate(Current_Date(), -1), `MyDateColumn`) < 28 and DateDiff(Current_Date(), `MyDateColumn`) > 0 then 'Last 28 Days'
when DateDiff(AddDate(Current_Date(), -1), `MyDateColumn`) < (28 + 28) and DateDiff(Current_Date(), `MyDateColumn`) > 28 then '4 Weeks Prior'
when when DateDiff(AddDate(Current_Date(), -1), `MyDateColumn`) < 28 + (52 * 7) and DateDiff(Current_Date(), `MyDateColumn`) > (52 * 7) then '52 Weeks Prior'
end
Calculating Percent Change from the Previous Year to the Current Year
Say you have a card that shows the last 24 months, grouped by month. You have the year set as the series. In the summary number you want to show the percent change from January through the current month of the previous year compared to January through the current month of the current year. To achieve this, you can create the following Beast Mode calculation:
(SUM(CASE WHEN YEAR('Date') = YEAR('NOW') THEN 'Total Sales' ELSE 0 END)-SUM(CASE WHEN YEAR('Date')= YEAR(DATE_SUB('NOW', INTERVAL 1 YEAR))AND month('Date') <= month('NOW') THEN 'Total Sales' ELSE 0 END))/
SUM(CASE WHEN YEAR('Date')= YEAR(DATE_SUB('NOW', INTERVAL 1 YEAR)) AND month('Date') <= month('NOW') THEN 'Total Sales' ELSE 0 END)
This calculation would be useful as a summary number on a card or as the value on a Single Value gauge card. If you’d like to chart the percent change over time, follow the instructions for advanced period-over-period calculations found in the previous section, then create a Beast Mode calculation to find the percent change from the fields created for the previous year’s value and the current year’s value following this formula:
(This Year’s Value – Last Year’s Value) / Last Year’s Value
Calculating Variance
Variance CY-PY
Use the following code to create a calculation for the variance Current Year - Past Year:
(
SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) THEN `Amount` ELSE 0 END)
- SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) - 1 THEN `Amount` ELSE 0 END)
)
Variance % ((CY - PY)/PY)
Use the following code to create a calculation for the variance % ((Current Year - Past Year) / Past Year):
(
(SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) THEN `Amount` ELSE 0 END)
- SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) - 1 THEN `Amount` ELSE 0 END)
)
/ NULLIF(SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) - 1 THEN `Amount` ELSE 0 END), 0)
)
Year-to-Date Variance
Let's say you have a DataSet with your employee count by store by month. How could you compare the current month's employee count to the year-end count?
Assuming your card is filtered to the current year, you could create the following Beast Mode calculations:
Employee Count This Month:
COUNT(DISTINCT CASE WHEN YEAR(DateColumn) = YEAR(CURRENT_DATE()) AND MONTH(DateColumn) = MONTH(CURRENT_DATE()) THEN `Employee ID` END)
Employee Count This Year:
COUNT(DISTINCT'Employee ID')
Employee Count This Year - Employee Count This Month (YTD Variance):
COUNT(DISTINCT `Employee Id` - COUNT(DISTINCT CASE WHEN YEAR(DateColumn) = YEAR(CURRENT_DATE()) AND MONTH(DateColumn) = MONTH(CURRENT_DATE()) THEN `Employee ID` END)
If you need this data to be trended over time, this solution will require doing some DataFlow work to summarize things at a monthly level with a reference to YTD for each month.
Calculating Year-over-Year (YOY) with Ratios
All calculations for ratios must be done in a DataFlow if using this type of comparison.
Video - Period Over Period with Beast Mode
YOY: Multi-Period Comparison
To create multi-period year-over-year calculations, refer to the following table:
| Period | Description | Calculation |
|---|---|---|
| By Year | Create a year Beast Mode and plot as category (x-axis on most charts). | year(`datefield`) |
| By Quarter | Create a Quarter Beast Mode and plot as category (x-axis on most charts), and if you’re spanning multiple years, use year(`datefield`) as the series. | Quarter(`datefield`) |
| By Month | Create a Month Beast Mode and plot as category (x-axis on most charts), and if you’re spanning multiple years, use year(`datefield`) as the series. | Month(`datefield`) |
| By Week | Create a Week Beast Mode and plot as category (x-axis on most charts), and if you’re spanning multiple years, use year(`datefield`) as the series. | WeekofYear(`datefield`) |
| By Day | Create a Day Beast Mode and plot as category (x-axis on most charts), and if you’re spanning multiple years, use year(`datefield`) as the series. | DayofYear(`datefield`) |
YOY: 2-Period Comparison
‘This Year’ and ‘Last Year’ can be swapped out for other text or amount fields. When running ratios, amount fields are needed.
The advantage of using the “Same Series" vs. the “Multiple Series" is that you can consolidate the code using the “Same Series”. However, if you’re swapping out ‘This Year’/‘Last Year’ for amount columns, these need to be separated.
YOY: Full-Year Comparison
To create full-year year-over-year calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series - Current Year | Case when Year(`Datefield`) = Year(curdate()) then 'This Year' end |
| Multiple Series - Previous Year | Case when Year(`Datefield`) = Year(curdate())-1 then 'Last Year' end |
| Multiple Series - Same Series | Case year(`Datefield`) when year(curdate()) then 'This Year' when year(curdate())-1 then 'Last Year’ End |
| Ratio: % change between the two using the formula (C/P) - 1 |
(Case when Year(`Datefield`) = Year(curdate()) then `Amountfield` end / Case when Year(`Datefield`) = Year(curdate())-1 then `Amountfield` end) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
YOY: Year-to-Date (YTD) Comparison
To create year-to-date year-over-year calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series - Current Year | CASE WHEN year(`Datefield`)=year(curdate()) THEN 'This Year' END |
| Multiple Series - Previous Year | CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (dayofyear(curdate()) >= dayofyear(`Datefield`))) THEN 'Last Year' END |
| Multiple Series - Same Series | CASE year(`Datefield`) when year(curdate()) THEN 'This Year’ when ((year(`Datefield`)=(year(curdate()) - 1)) AND (dayofyear(curdate()) >= dayofyear(`Datefield`))) THEN 'Last Year' END |
| Ratio: % change between the two using the formula (C/P) - 1 |
(CASE WHEN year(`Datefield`)=year(curdate()) THEN `Amountfield` END / CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (dayofyear(curdate()) >= dayofyear(`Datefield`))) THEN `Amountfield` END) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
YOY: Quarter Comparison
To create quarterly year-over-year calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series - Current Year | Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' end |
| Multiple Series - Previous Year | Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())-1) then 'Last Year' end |
| Multiple Series - Same Series | Case when (Quarter(`Datefield`) = Quarter(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' when (Quarter(`Datefield`) = Quarter(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End |
| Ratio: % change between the two using the formula (C/P) - 1 |
(Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())) then `Amountfield` end / Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())-1) then `Amountfield` end) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
YOY: Quarter-to-Date (QTD) Comparison
To create quarter-to-date year-over-year calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series - Current Year | Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate()) and dayofyear(`Datefield`) <= dayofyear(curdate())) then 'This Year' end |
| Multiple Series - Previous Year | Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())-1 and dayofyear(`Datefield`) <= dayofyear(curdate())) then 'Last Year' end |
| Multiple Series - Same Series | Case when (Quarter(`Datefield`) = Quarter(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' when (Quarter(`Datefield`) = Quarter(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End |
| Ratio: % change between the two using the formula (C/P) - 1 |
(Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate()) and dayofyear(`Datefield`) <= dayofyear(curdate())) then `Amountfield` end / Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())-1 and dayofyear(`Datefield`) <= dayofyear(curdate())) then`Amountfield` end) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
YOY: Month Comparison
To create monthly year-over-year calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series - Current Year | Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' end |
| Multiple Series - Previous Year | Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate())-1) then 'Last Year' end |
| Multiple Series - Same Series | Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End |
| Ratio: % change between the two using the formula (C/P) - 1 |
(Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate())) then `Amountfield` end / Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate())-1) then `Amountfield` end) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
YOY: Month-to-Date (MTD) Comparison
To create month-to-date year-over-year calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series - Current Year | Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then 'This Year' end |
| Multiple Series - Previous Year | Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year)) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then 'Last Year' end |
| Multiple Series - Same Series | Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then 'This Year' when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year)) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then Last Year' End |
| Ratio: % change between the two using the formula (C/P) - 1 |
(Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then `Amountfield` end / Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year)) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then `Amountfield` end) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
YOY: Week Comparison
To create month-to-date year-over-year calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series - Current Year | Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year’ end |
| Multiple Series - Previous Year | Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End |
| Multiple Series - Same Series | Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year’ End |
| Ratio: % change between the two using the formula (C/P) - 1 |
((Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then `Amountfield` end / Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then `Amountfield` End) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
YOY: Week-to-Date (WTD) Comparison
To create week-to-date year-over-year calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series - Current Year | Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then 'This Year' end |
| Multiple Series - Previous Year | Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year)) and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then 'Last Year' End |
| Multiple Series - Same Series | Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then 'This Year' when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then 'Last Year’ End |
| Ratio: % change between the two using the formula (C/P) - 1 |
(Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then `Amountfield` end / Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year)) and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then `Amountfield` End) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
YOY: Day Comparison
To create week-to-date year-over-year calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series - Current Year | Case when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' end |
| Multiple Series - Previous Year | Case when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End |
| Multiple Series - Same Series | Case when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End |
| Ratio: % change between the two using the formula (C/P) - 1 |
(Case when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then `Amountfield` end / Case when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then `Amountfield` End) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
POP Calculations with Ratio Calculations (Current Period vs. Last)
POP: Quarter-over-Quarter (QOQ) Comparison
To create quarter-over-quarter comparison calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series - Current Period | CASE WHEN ((year(`Datefield`)=year(curdate())) AND (quarter(`Datefield`)=quarter(CURDATE()))) THEN `Amountfield` END |
| Multiple Series - Previous Period | CASE WHEN (quarter(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (quarter(`Datefield`)=4)) THEN `Amountfield` END ) ELSE (CASE WHEN ((year(`Datefield`)=year(curdate())) AND (quarter(`Datefield`)=(quarter(CURDATE()) -1))) THEN `Amountfield` END ) END |
| Ratio: % change between the two using the formula (C/P) - 1 |
(CASE WHEN ((year(`Datefield`)=year(curdate())) AND (quarter(`Datefield`)=quarter(CURDATE()))) THEN `Amountfield` END / CASE WHEN (quarter(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (quarter(`Datefield`)=4)) THEN `Amountfield` END ) ELSE (CASE WHEN ((year(`Datefield`)=year(curdate())) AND (quarter(`Datefield`)=(quarter(CURDATE()) -1))) THEN `Amountfield` END ) END) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
POP: QTD Quarter-over-Quarter Comparison
To create quarter-to-date quarter-over-quarter comparison calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series - Current Period | CASE WHEN ((year(`Datefield`)=year(curdate())) AND (quarter(`Datefield`)=quarter(CURDATE()))) THEN `Amountfield` END |
| Multiple Series - Previous Period |
MAX(CASE WHEN YEAR(curdate()) / 4 = ROUND((YEAR(curdate()) / 4),0) THEN CASE WHEN QUARTER(curdate())=1 THEN (DAYOFYEAR(curdate()) - 1) WHEN (QUARTER(curdate())=2) THEN (DAYOFYEAR(curdate()) - 91) WHEN (QUARTER(curdate())=3) THEN (DAYOFYEAR(curdate()) - 182) WHEN (QUARTER(curdate())=4) THEN (DAYOFYEAR(curdate()) - 273) ELSE 0 END ELSE CASE WHEN QUARTER(curdate())=1 THEN DAYOFYEAR(curdate()) - 1 WHEN QUARTER(curdate())=2 THEN DAYOFYEAR(curdate()) - 90 WHEN QUARTER(curdate())=3 THEN DAYOFYEAR(curdate()) - 181 WHEN QUARTER(curdate())=4 THEN DAYOFYEAR(curdate()) - 272 ELSE 0 END END) * MAX(CASE WHEN YEAR(curdate()) / 4=ROUND(YEAR(curdate()) / 4,0) THEN CASE WHEN (QUARTER(curdate())=1) THEN 91 WHEN (QUARTER(curdate())=2) THEN 91 WHEN (QUARTER(curdate())=3) THEN 92 WHEN (QUARTER(curdate())=4) THEN 92 ELSE 0 END ELSE CASE WHEN (QUARTER(curdate())=1) THEN 90 WHEN (QUARTER(curdate())=2) THEN 91 WHEN (QUARTER(curdate())=3) THEN 92 WHEN (QUARTER(curdate())=4) THEN 92 ELSE 0 END END) - MAX(CASE WHEN YEAR(curdate()) / 4=ROUND(YEAR(curdate()) / 4,0) THEN CASE WHEN QUARTER(curdate())=1 THEN DAYOFYEAR(curdate()) - 1 WHEN QUARTER(curdate())=2 THEN DAYOFYEAR(curdate()) - 91 WHEN QUARTER(curdate())=3 THEN DAYOFYEAR(curdate()) - 182 WHEN QUARTER(curdate())=4 THEN DAYOFYEAR(curdate()) - 273 ELSE 0 END ELSE CASE WHEN QUARTER(curdate())=1 THEN DAYOFYEAR(curdate()) - 1 WHEN QUARTER(curdate())=2 THEN DAYOFYEAR(curdate()) - 90 WHEN QUARTER(curdate())=3 THEN DAYOFYEAR(curdate()) - 181 WHEN QUARTER(curdate())=4 THEN DAYOFYEAR(curdate()) - 272 ELSE 0 END END) + SUM(CASE WHEN QUARTER(curdate())=QUARTER(`Datefield`) AND YEAR(`Datefield`)=YEAR(curdate()) THEN `Amountfield` ELSE 0 END) - SUM((CASE WHEN ((QUARTER(curdate())=QUARTER(`Datefield`)) AND (YEAR(`Datefield`)=YEAR(DATE_SUB(curdate(),INTERVAL 1 QUARTER)))) THEN `Amountfield` ELSE 0 END ))) / SUM((CASE WHEN QUARTER(DATE_SUB(curdate(),INTERVAL 1 QUARTER))=QUARTER(`Datefield`) AND YEAR(`Datefield`)=YEAR(DATE_SUB(curdate(),INTERVAL 1 QUARTER)) THEN `Amountfield` ELSE 0 END )) |
| Ratio: % change between the two using the formula (C/P) - 1 |
Take the formula above and insert the previous two sections to get the result. Note: Can also be done using (C-P)/P but this takes more code.
|
POP: Month-over-Month (MOM) Comparison
To create month-over-month comparison calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series |
CASE WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=month(CURDATE()))) THEN `Amountfield` END CASE WHEN (month(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (month(`Datefield`)=12)) THEN `Amountfield` END ) ELSE (CASE WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=(month(CURDATE()) -1))) THEN `Amountfield` END ) END |
| Ratio: % change between the two using the formula (C/P) - 1 |
(CASE WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=month(CURDATE()))) THEN `Amountfield` END / CASE WHEN (month(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (month(`Datefield`)=12)) THEN `Amountfield` END ) ELSE (CASE WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=(month(CURDATE()) -1))) THEN `Amountfield` END ) END) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
POP: Month-to-Date MOM Comparison
To create month-to-date month-over-month comparison calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series |
CASE WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=month(CURDATE()))) THEN `Amountfield` END CASE WHEN month(curdate()) = 1 THEN CASE WHEN year(`Datefield`) = year(curdate()) - 1 AND month(`Datefield`) = 12 AND DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate()) THEN `Amountfield` END ELSE CASE WHEN year(`Datefield`) = year(curdate()) AND month(`Datefield`) = month(curdate())-1 AND DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate()) THEN `Amountfield` END END |
| Ratio: % change between the two using the formula (C/P) - 1 |
(CASE WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=month(CURDATE()))) THEN `Amountfield` END / CASE WHEN month(curdate()) = 1 THEN CASE WHEN year(`Datefield`) = year(curdate()) - 1 AND month(`Datefield`) = 12 AND DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate()) THEN `Amountfield` END ELSE CASE WHEN year(`Datefield`) = year(curdate()) AND month(`Datefield`) = month(curdate())-1 AND DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate()) THEN `Amountfield` END END) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
POP: Week-over-Week (WOW) Comparison
To create week-over-week comparison calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series |
CASE WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=weekofyear(CURDATE()))) THEN `Amountfield` END CASE WHEN (weekofyear(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (weekofyear(`Datefield`)=52)) THEN `Amountfield` END ) ELSE (CASE WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=(weekofyear(CURDATE()) -1))) THEN `Amountfield` END ) END |
| Ratio: % change between the two using the formula (C/P) - 1 |
(CASE WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=weekofyear(CURDATE()))) THEN `Amountfield` END / CASE WHEN (weekofyear(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (weekofyear(`Datefield`)=52)) THEN `Amountfield` END ) ELSE (CASE WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=(weekofyear(CURDATE()) -1))) THEN `Amountfield` END ) END) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
POP: Week-to-Date WOW Comparison
To create week-to-date week-over-week comparison calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series |
CASE WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=weekofyear(CURDATE()))) THEN `Amountfield` END CASE WHEN weekofyear(curdate()) = 1 THEN CASE WHEN year(`Datefield`) = year(curdate()) - 1 AND weekofyear(`Datefield`) = 52 AND DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate()) THEN `Amountfield` END ELSE CASE WHEN year(`Datefield`) = year(curdate()) AND weekofyear(`Datefield`) = weekofyear(CURDATE()) -1 AND DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate()) THEN `Amountfield` END END |
| Ratio: % change between the two using the formula (C/P) - 1 |
(CASE WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=weekofyear(CURDATE()))) THEN `Amountfield` END / CASE WHEN weekofyear(curdate()) = 1 THEN CASE WHEN year(`Datefield`) = year(curdate()) - 1 AND weekofyear(`Datefield`) = 52 AND DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate()) THEN `Amountfield` END ELSE CASE WHEN year(`Datefield`) = year(curdate()) AND weekofyear(`Datefield`) = weekofyear(CURDATE()) -1 AND DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate()) THEN `Amountfield` END END) -1 Note: Can also be done using (C-P)/P but this takes more code.
|
POP: Day-over-Day (DOD) Comparison
To create day-over-day comparison calculations, refer to the following table:
| Type | Calculation |
|---|---|
| Multiple Series |
CASE WHEN ((year(`Datefield`)=year(curdate())) AND (dayofyear(`Datefield`)=dayofyear(CURDATE()))) THEN `Amountfield` END CASE WHEN (dayofyear(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (dayofyear(`Datefield`)=365)) THEN `Amountfield` END ) ELSE (CASE WHEN ((year(`Datefield`)=year(curdate())) AND (dayofyear(`Datefield`)=(dayofyear(CURDATE()) -1))) THEN `Amountfield` END ) END |
| Ratio: % change between the two using the formula (C/P) - 1 |
(CASE WHEN ((year(`Datefield`)=year(curdate())) AND (dayofyear(`Datefield`)=dayofyear(CURDATE()))) THEN `Amountfield` END / CASE WHEN (dayofyear(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (dayofyear(`Datefield`)=365)) THEN `Amountfield` END ) ELSE (CASE WHEN ((year(`Datefield`)=year(curdate())) AND (dayofyear(`Datefield`)=(dayofyear(CURDATE()) -1))) THEN `Amountfield` END ) END) -1 Note: Can also be done using (C-P)/P but this takes more code.
|