Beast Mode Week Revenue Calculation

user04420

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

BlueRooster

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

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

 

user04420

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

 

 

BlueRooster

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

user04420

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

user04420

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