# Beast Mode Week Revenue Calculation

Hi folks,

This should be a fairly straight forward question. So I need to do a calculation that finds the revenue from 12 weeks ago. I know that I can use weekofyear(). However, since it's only 6 weeks into 2018. This method wouldn't work untill later in the year. Is there a beast mode function that allows me to actually get the weeks from 2017?

Thanks,

Leon

• Coach You could do something like this:

`CASE WHEN WEEK(DATE_SUB(Current_Date(), INTERVAL 12 WEEK)) = WEEK(`DateField`) AND YEAR(DATE_SUB(Current_Date(), INTERVAL 12 WEEK)) = YEAR(`DateField`) THEN `Revenue` END`

That would compare the week # and year from 12 weeks ago to each date field's week # and year. If matching then it would return your revenue.

Is that what you had in mind?

Sincerely,
ValiantSpur

**Say "Thanks" by clicking the "heart" in the post that helped you.

• Hi,

Thanks so much! But apparently this did not work out too well. I think the date_sub function is not used properly here. Please see my code for the details.

CASE WHEN
sum(CASE WHEN WEEKOFYEAR(`day`) = WEEKOFYEAR(CURRENT_DATE()) - 1
THEN `grossRevenue`
END) > 100
AND
sum(CASE WHEN WEEK(DATE_SUB(Current_Date(), INTERVAL 12 WEEK)) = WEEK(`day`) AND YEAR(DATE_SUB(Current_Date(), INTERVAL 12 WEEK)) = YEAR(`day`) THEN `grossRevenue` END) < 10

THEN
sum(CASE WHEN WEEKOFYEAR(`day`) = WEEKOFYEAR(CURRENT_DATE()) - 1
THEN `grossRevenue`
END)
END

Thanks,

Leon

• Coach Can you walk me through what you're trying to do with your code?

As I'm reading it sounds like:

When the sum of grossrevenue (From weeks prior to the current week) > 100

AND

When the sum of grossrevenue (from 12 weeks ago) < 10

THEN

Sum grossrevenue prior to current week

Is there a reason you aren't just using

`sum(CASE WHEN WEEK(DATE_SUB(Current_Date(), INTERVAL 12 WEEK)) = WEEK(`day`) AND YEAR(DATE_SUB(Current_Date(), INTERVAL 12 WEEK)) = YEAR(`day`) THEN `grossRevenue` END)`

to get your gross revenue from 12 weeks ago?

I

• Hi.

You are totally right! The problem is that the code didn't return anything. So what I'm trying to do is to get revenue from 12 weeks ago to compare it to the revenue from last week.

Cheers,

Leon

• `sum(CASE WHEN WEEK(DATE_SUB(Current_Date(), INTERVAL 12 WEEK)) = WEEK(`day`) AND YEAR(DATE_SUB(Current_Date(), INTERVAL 12 WEEK)) = YEAR(`day`) THEN `grossRevenue` END)`

did not give me the revenue from 12 weeks ago for some reason