Comparitve Gauge Last Week Vs This Week

Mike_Shrift
Mike_Shrift Member
in Charting

I'm trying to create a card to show last week's sales Vs current week's sales, I'm but having issues with the values my beast modes are returning.


The week should start on Monday and end on Sunday. I have our fiscal calendar enabled and checked so I'm not sure if that's causing the issue or not.


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 `premium_amount` END))


This Week: SUM((CASE WHEN (((Year(`filter_date`) = Year(CURDATE())) AND (WEEKOFYEAR(`filter_date`) = WEEKOFYEAR(CURDATE()))) AND (DAYOFWEEK(`filter_date`) <= (DAYOFWEEK(CURRENT_DATE) - 1))) THEN `premium_amount` END))

Tagged:

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @Mike_Shrift

    What specific issues are you having with the values? Without understanding the problem it's hard to help fix it.

    Whenever I'm doing Period over Period analysis I utilize a custom date dimension table to help identify this week vs last week for example. I've outlined this process before in the dojo here: A more flexible way to do Period over Period comparisons

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

    Hey, @GrantSmith I'll take a look at the link and let you know if I have any questions.

    I'm just not getting the correct results for last week's sales or this week's sales from either of those.

    I'm not having any issues with Month to Date and Last Month. I just can't figure out these weekly ones for some reason.

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I plugged your beast modes into a card and replaced your fields with fields from my dataset and I got the correct numbers I would expect for my dataset. That tells me there is likely a data issue either with the filter dates or the premium amounts. I would also check for what field is used in your date range filter and if you have anything in your sorting or filtering properties that would affect it.

    I'm also not sure your formulas will work for when the end of the year comes and you need to compare the first week of January with the last week of December. You may want to think about a different option so it will handle that cleanly.

    **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.

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @Mike_Shrift

    What specific issues are you having with the values? Without understanding the problem it's hard to help fix it.

    Whenever I'm doing Period over Period analysis I utilize a custom date dimension table to help identify this week vs last week for example. I've outlined this process before in the dojo here: A more flexible way to do Period over Period comparisons

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

    Hey, @GrantSmith I'll take a look at the link and let you know if I have any questions.

    I'm just not getting the correct results for last week's sales or this week's sales from either of those.

    I'm not having any issues with Month to Date and Last Month. I just can't figure out these weekly ones for some reason.

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I plugged your beast modes into a card and replaced your fields with fields from my dataset and I got the correct numbers I would expect for my dataset. That tells me there is likely a data issue either with the filter dates or the premium amounts. I would also check for what field is used in your date range filter and if you have anything in your sorting or filtering properties that would affect it.

    I'm also not sure your formulas will work for when the end of the year comes and you need to compare the first week of January with the last week of December. You may want to think about a different option so it will handle that cleanly.

    **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.

Categories