Week Grouping for Date Add Function

BTPG
BTPG ⚪️
edited January 4 in Beastmode & Analytics

I've created a beast mode to add 2 working days to a date.

CASE WHEN `actual arrival at pod date` IS NULL THEN (DATE_ADD(`estimated arrival at pod date`, INTERVAL (FLOOR(2/5) * 7 + CASE WHEN MOD(2,5) + DAYOFWEEK(`estimated arrival at pod date`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`estimated arrival at pod date`)/7) ELSE 0 END + MOD(2,5)) DAY))

WHEN `actual arrival at pod date` IS NOT NULL THEN (DATE_ADD(`actual arrival at pod date`, INTERVAL (FLOOR(2/5) * 7 + CASE WHEN MOD(2,5) + DAYOFWEEK(`actual arrival at pod date`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`actual arrival at pod date`)/7) ELSE 0 END + MOD(2,5)) DAY)) END

This has returned the correct data.

However I then want to group it into calendar weeks to visualize on Vertical Bar graph but when I open the week the start date of the week is the Thursday, not the Monday.

I used the below beast mode which is a beast mode inside a beast mode. Is this even possible.

WEEKOFYEAR(DATE(DATE_ADD(`estimated arrival at pod date`, INTERVAL (FLOOR(4/5) * 7 + CASE WHEN MOD(4,5) + DAYOFWEEK(`estimated arrival at pod date`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`estimated arrival at pod date`)/7) ELSE 0 END + MOD(4,5)) DAY)))