Week to Week Sales Comparison

Mike_Shrift

I'm looking for a way to use beast mode to calculate last week's sales and compare them to this week's sales.

The syntax I used for the monthly calculation worked but I'm failing to make the weekly one work.

Last Month:

SUM((CASE WHEN (month(curdate()) = 1) THEN (CASE WHEN ((year(`filter_date`) = (year(curdate()) - 1)) AND (month(`filter_date`) = 12)) THEN (`premium_amount` * 1) END) WHEN ((Year(`filter_date`) = Year(CURDATE())) AND (MONTH(`filter_date`) = (Month(CURDATE()) - 1))) THEN (`sales` * 1) END))

MTD:

(SUM((CASE WHEN (((Year(`filter_date`) = Year(CURDATE())) AND (MONTH(`filter_date`) = Month(CURDATE()))) AND (DAYOFMONTH(`filter_date`) <= (DAYOFMONTH(CURRENT_DATE) - 1))) THEN `sales` END)) * 1)

Last week:

SUM((CASE WHEN (((YEAR(`filter_date`) = YEAR(CURDATE())) AND (WEEKOFYEAR(`filter_date`) = (WEEKOFYEAR(CURDATE()) - 1))) AND (DAYOFWEEK(`filter_date`) <= (DAYOFWEEK(CURRENT_DATE) - 1))) THEN `sales` END))

WTD:

SUM((CASE WHEN (((Year(`filter_date`) = Year(CURDATE())) AND (WEEKOFYEAR(`filter_date`) = WEEKOFYEAR(CURDATE()))) AND (DAYOFWEEK(`filter_date`) <= (DAYOFWEEK(CURRENT_DATE) - 1))) THEN (`sales` * 1) END))

MarkSnodgrass

Are you getting a syntax error? Or is the result just not want you are expecting?

There are a couple other date functions you might want to consider using to make the function a little easier to read.

You might consider using DATE_SUB to get the previous month and previous week as it has an interval parameter that lets you specify week or month for how much you want to subtract.

You could also use the YEARWEEK function as it will put it in YYYYWW format, such 202116 for the 16th week of 2021. You can then just subtract 1 for the previous week.

These functions would reduce the number of ANDs that you have to determine if you are looking at the correct date.

Mike_Shrift

Hey @MarkSnodgrass ,

No syntax error it's just not producing accurate results. I will give your suggestions a try...

GrantSmith

Hi @Mike_Shrift ,

I cases of period over period I highly recommend creating a date dimension table to join to with your different offsets (day, week, year, etc) This gives you more flexibility with your graphs. Plus if you're looking to do period over period this way give more accurate representation of the current month as it'll compare the same number of days and not a whole prior month to a current partial month. You'll just need to use a Line + Bar type graph instead of a Period over Period graph.

I've done a prior writeup of this under https://dojo.domo.com/discussion/comment/50540#Comment_50540