Week to Week Sales Comparison

Mike_Shrift
Mike_Shrift Member
edited April 2021 in Magic ETL

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))

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Mike_Shrift
    Mike_Shrift Member

    Hey @MarkSnodgrass ,

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

  • GrantSmith
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories