Cumulative Sales between December and January not calculating correctly.

tlammie1806

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.

Daily & Cumulative Sales Comparison.xlsx

Uploaded Jan 4, 20248.47 KB

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!

marcel_luthi

The problem here is that when you use DATE_FORMAT the output is no longer a date but a STRING, and the sorting of it is not the same as the sorting you're displaying in your table. You'll need to play with your ORDER BY clause to make sure that 01/01 comes after 12/31 (I'd usually use a year indicator like 01 and 02), haven't tried but something like this might work:

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,'02/%m/%d')
WHEN DATE(created_at) > '2023-02-01' THEN DATE_FORMAT(created_at,'01/%m/%d')
WHEN YEAR(created_at) = '2023' AND DAYOFYEAR(created_at) <= DAYOFYEAR(CURDATE()) THEN DATE_FORMAT(created_at,'02/%m/%d')
WHEN YEAR(created_at) = '2022' AND DAYOFYEAR(created_at) > DAYOFYEAR(CURDATE())  THEN DATE_FORMAT(created_at,'01/%m/%d')
END)

marcel_luthi

The problem here is that when you use DATE_FORMAT the output is no longer a date but a STRING, and the sorting of it is not the same as the sorting you're displaying in your table. You'll need to play with your ORDER BY clause to make sure that 01/01 comes after 12/31 (I'd usually use a year indicator like 01 and 02), haven't tried but something like this might work:

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,'02/%m/%d')
WHEN DATE(created_at) > '2023-02-01' THEN DATE_FORMAT(created_at,'01/%m/%d')
WHEN YEAR(created_at) = '2023' AND DAYOFYEAR(created_at) <= DAYOFYEAR(CURDATE()) THEN DATE_FORMAT(created_at,'02/%m/%d')
WHEN YEAR(created_at) = '2022' AND DAYOFYEAR(created_at) > DAYOFYEAR(CURDATE())  THEN DATE_FORMAT(created_at,'01/%m/%d')
END)