Sum Previous 4 Weeks of Sales Together

user17569
user17569 Member
in Magic ETL

Hi,

 

I'm trying to sum our weekly sales data into 4 weeks of sales. For example, if we had $10 in sales each of the past 4 weeks, the value returnded should be $40.

 

I'm using this function: 

 

SUM(CASE

WHEN WEEK('Week Beginning') > WEEK('Week Beginning') - 4

THEN 'Sales'

ELSE 0

END)

 

But this is just returning the sales for last week, not the aggregate of the past 4 weeks.

 

Thank you

Comments

  • DataMaven
    DataMaven Coach
    We may need to see a sample of columns and values.

    That being said, try putting a MAX function in front of the second WEEK statement. This way, you're taking all the weeks which are within 4 weeks of the most current week.

    At some point, you'll need to aggregate the Sales, but perhaps you're doing that in the output. If it's not working try summing the THEN.
    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • rado98
    rado98 Contributor

    Hi

    The Max Function would not work in this case

    Try using 'Today' instead of 'Week Beginning' on the second instance.

    Also try addign YEAR() to the beastmode otherwise the logic will break during the first 3 weeks of the year

     

  • user17569
    user17569 Member

    Thanks All! I'm going to play around with your suggestions and see how it turns out. 

     

     

  • DataMaven
    DataMaven Coach

    Good thinking @rado98 on those first few weeks!  

    @user17569 - Make sure to let us know what works, and if there's anything we can add.  

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories