Beast Mode Week Revenue Calculation

user04420
user04420 Member
in Archive

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

Comments

  • BlueRooster
    BlueRooster Domo Employee

    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
    user04420 Member

    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
    BlueRooster Domo Employee

    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
    user04420 Member

    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
    user04420 Member 
    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 

Categories