Rolling Count Current Week

Reply
Yellow Belt

Rolling Count Current Week

I am trying to create a table with rolling count of the current week, last 4 weeks, and last 8 weeks. I was able to create beast mode calculations for last 4 & 8 weeks, but I am lost in creating beast mode calculation for the current week. What would be the best way to calculate rolling count for the current week? Thank you in advance!

 

Below are the calculations for rolling 4 weeks and 8 weeks:

SUM(
CASE WHEN (`Date`) > DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK)
AND (`Date`) <= CURRENT_DATE()
THEN `Units`
END)

 

SUM(
CASE WHEN (`Date`) > DATE_SUB(CURRENT_DATE(), INTERVAL 8 WEEK)
AND (`Date`) <= CURRENT_DATE()
THEN `Units`
END)


Accepted Solutions
Black Belt

Hi @user069636 

You can calculate the first day in a week using the DAYOFWEEK function in your beast mode.

 

SUM(
CASE WHEN (`Date`) > DATE_SUB(CURRENT_DATE(), INTERVAL DAYOFWEEK(CURRENT_DATE()) DAY)
AND (`Date`) <= CURRENT_DATE()
THEN `Units`
END)

 

DAYOFWEEK returns values between 1 (Sunday) and 7 (Saturday). Subtracting that value for the number of days will return the end of last week so you just need to check to see if the date is after the end of last week.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post


All Replies
Black Belt

Hi @user069636 

You can calculate the first day in a week using the DAYOFWEEK function in your beast mode.

 

SUM(
CASE WHEN (`Date`) > DATE_SUB(CURRENT_DATE(), INTERVAL DAYOFWEEK(CURRENT_DATE()) DAY)
AND (`Date`) <= CURRENT_DATE()
THEN `Units`
END)

 

DAYOFWEEK returns values between 1 (Sunday) and 7 (Saturday). Subtracting that value for the number of days will return the end of last week so you just need to check to see if the date is after the end of last week.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Announcements
New & Improved Dojo: Get excited for a new feel and additional features in the online community.

Site will be down February 4-5th for maintenance.

Launch coming February 8th!