Cumulative Sales between December and January not calculating correctly.

tlammie1806
tlammie1806 Member
in Charting

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:

image.png

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.

image.png

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

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)
image.png

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!

Tagged:

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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)

Answers

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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)

Categories