Year-over-Year Sales Unit Comparison

German_Rodriguez

This comprehensive report is crucial for businesses aiming to track performance and make informed decisions. By comparing sales units from the current period against the same period last year, stakeholders can gauge the effectiveness of their strategies, identify trends, and adjust operations to enhance profitability.

The report includes daily, weekly, monthly, quarterly, and annual comparisons, providing a holistic view of the business's trajectory and market dynamics. Understanding these metrics helps in optimizing inventory management, marketing campaigns, and sales tactics to better align with consumer demands and market conditions.

Columns and Their Formulas for the Report:

simple_timestamp: The actual date for the data entry.
Day Name: The name of the day for simple_timestamp.
1 Yesterday Units (This Year)
SUM(CASE
WHEN DATE(simple_timestamp) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 day)
THEN (quantity)
END)

1 Yesterday Units (LY)
SUM(CASE
WHEN DAYOFWEEK(simple_timestamp) = DAYOFWEEK(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND WEEKOFYEAR(simple_timestamp) = WEEKOFYEAR(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND YEAR(simple_timestamp) = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR))
THEN quantity
END)

1 Yesterday Units Delta %
(1 Yesterday Units (This Year) - 1 Yesterday Units (LY)) / 1 Yesterday Units (LY)

2 TWTD Units (This Year)
SUM(CASE
WHEN YEAR(simple_timestamp) = YEAR(CURRENT_DATE())
AND WEEKOFYEAR(simple_timestamp) = WEEKOFYEAR(CURRENT_DATE())
THEN quantity
END)

2 TWLY Units (Last Year)
SUM(CASE
WHEN YEAR(simple_timestamp) = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR))
AND WEEKOFYEAR(simple_timestamp) = WEEKOFYEAR(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR))
AND DAYOFWEEK(simple_timestamp) <= DAYOFWEEK(CURRENT_DATE() - INTERVAL 1 DAY) -- Using days completed this year
AND DAYOFWEEK(simple_timestamp) >= 2 -- Assuming the week starts on Monday (2)
THEN quantity
END)

2 TW Units Delta %
(2 TWTD Units (This Year) - 2 TWLY Units (Last Year)) / 2 TWLY Units (Last Year)

3 MTD Units (This Year)
SUM(CASE
WHEN YEAR(simple_timestamp) = YEAR(CURRENT_DATE())
AND MONTH(simple_timestamp) = MONTH(CURRENT_DATE())
AND simple_timestamp <= CURRENT_DATE()
THEN (quantity)
END)

3 MTD Units (LY)
SUM(CASE
WHEN YEAR(simple_timestamp) = YEAR(CURRENT_DATE()) - 1
AND MONTH(simple_timestamp) = MONTH(CURRENT_DATE())
AND DAYOFMONTH(simple_timestamp) < DAYOFMONTH(CURRENT_DATE())
THEN (quantity)
END)

3 MTD Units Delta %
(3 MTD Units (This Year) - 3 MTD Units (LY)) / 3 MTD Units (LY)

4 QTD Units (This Year)
SUM(CASE
WHEN YEAR(simple_timestamp) = YEAR(CURRENT_DATE())
AND QUARTER(simple_timestamp) = QUARTER(CURRENT_DATE())
AND simple_timestamp <= CURRENT_DATE()
THEN (quantity)
END)

4 QTD Units (LY)
SUM(CASE
WHEN YEAR(simple_timestamp) = YEAR(CURRENT_DATE()) - 1
AND QUARTER(simple_timestamp) = QUARTER(CURRENT_DATE())
AND simple_timestamp < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
THEN (quantity)
END)

4 QTD Units Delta %
(4 QTD Units (This Year) - 4 QTD Units (LY)) / 4 QTD Units (LY)

5 YTD Units (This Year)
SUM(CASE
WHEN YEAR(simple_timestamp) = YEAR(CURRENT_DATE())
AND simple_timestamp <= CURRENT_DATE()
THEN (quantity)
END)

5 YTD Units (LY)
SUM(CASE
WHEN YEAR(simple_timestamp) = YEAR(CURRENT_DATE()) - 1
AND simple_timestamp < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
THEN (quantity)
END)

5 YTD Units Delta %
(5 YTD Units (This Year) - 5 YTD Units (LY)) / 5 YTD Units (LY)