Comparitve Gauge Last Week Vs This Week

Mike_Shrift

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

GrantSmith

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

Mike_Shrift

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

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.

GrantSmith

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

Mike_Shrift

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

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.