Hello,
I'm trying to create a table with daily net sales by date and also cumulative sales up to the current date (1/3 ATM) between two years:
For the "Date" column, I used a beast mode to create date index formatted as %m/%d value:
CASE
WHEN YEAR(`created_at`) = '2024' AND DAYOFYEAR(`created_at`) <= DAYOFYEAR(CURDATE()) THEN DATE_FORMAT(`created_at`'%m/%d')
WHEN DATE(`created_at`) > '2023-02-01' THEN DATE_FORMAT(`created_at`'%m/%d')
WHEN YEAR(`created_at`) = '2023' AND DAYOFYEAR(`created_at`) <= DAYOFYEAR(CURDATE()) THEN DATE_FORMAT(`created_at`'%m/%d')
WHEN YEAR(`created_at`) = '2022' AND DAYOFYEAR(`created_at`) > DAYOFYEAR(CURDATE()) THEN DATE_FORMAT(`created_at`'%m/%d')
END
Given this "Date" column, I filtered out any rows that are blank, which would remove sales for the period after 1/4 in 2023.
My current formula to calculate the cumulative sales is below where the same "Date" field above is used in the ORDER BY clause:
SUM(SUM(CASE WHEN DATE(created_at) < '2023-02-01' THEN order_net END ))
OVER (PARTITION BY YEAR(created_at) ORDER BY
CASE
WHEN YEAR(created_at) = '2024' AND DAYOFYEAR(created_at) <= DAYOFYEAR(CURDATE()) THEN DATE_FORMAT(created_at,'%m/%d')
WHEN DATE(created_at) > '2023-02-01' THEN DATE_FORMAT(created_at,'%m/%d')
WHEN YEAR(created_at) = '2023' AND DAYOFYEAR(created_at) <= DAYOFYEAR(CURDATE()) THEN DATE_FORMAT(created_at,'%m/%d')
WHEN YEAR(created_at) = '2022' AND DAYOFYEAR(created_at) > DAYOFYEAR(CURDATE()) THEN DATE_FORMAT(created_at,'%m/%d')
END)
This formula seems to work well up until Jan, at which point sales from 1/1 to 1/3 aren't added to the total cumulative sales from December but instead seems to be added to September when sales started for the year. Attached is the data from the chart.
Initially, the chart was ordered to have 1/1 to 1/4 at the bottom, but I achieved this sort order by using two helper beast modes Sort Month and Sort Day:
CASE WHEN MONTH(created_at) = 1 THEN 13 ELSE MONTH(created_at) END
and
DAY(created_at)
I'm not sure where to go from here, and can't find a way to get cumulative sales to add correctly. Any suggestions would be greatly appreciated!