Cumulative Sales between December and January not calculating correctly.
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!
Best 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)1
Answers
-
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)1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive