Sum Previous 4 Weeks of Sales Together

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

Find more posts tagged with

Comments

DataMaven

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.

rado98

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

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

DataMaven

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.