RocketMichael Member

@GrantSmith It’s a date column. I had a typo there btw. It’s 12-1-22. I’m basically saying it should be viewed as the previous month but for some reason isn’t.

I entered this, and 12-1-21 was processed as Null. Is this because Jan is 1 and Dec is 12 numerically- or is something else going wrong here? CASE WHEN `Month Date`= CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY THEN 'Last Month' ELSE null END

@GrantSmith I think this is something glitchy in the ETL itself. I used your formula and changed 'NOT' to null in a Formula Tile CASE WHEN `Week Date` = (CURDATE() - INTERVAL (DAYOFWEEK(CURDATE) + 6) DAY) THEN 'Last Week' ELSE null END I then used a filter tile to eliminate the nulls with IS NOT NULL. This did solve the…

@GrantSmith , just in case it impacts this solution- this is an ETL filter tile and not a beast mode. I can confirm I am definitely not converting this date field to a boolean.

@GrantSmith Thanks for your input on this. I got an error trying that out: Failed to convert 'NOT' from type 'string' to type 'boolean' I also wanted to share more details- just in case they are contributing to any issues here. The data is grouped and aggregated by week using the start date of each week before hitting this…

@GrantSmith Brilliant! Thank you. I was making this too complicated trying to figure out a way to make a dynamic constant. This is perfect. 👌

Hi Travor! Thanks for your reply and explanation. Apologies, I might not have explained this clearly enough. This sort of solution seems like the right path to me, though. I want to compare every period with the current week. So, in the example you created, the third column would read 222405 for every row. What's the best…

Makes total sense. I started to go into group functions and knew the data wouldn't be accurate... and paused. One more step to re-join with the original data will do the trick! Thanks so much!

@MarkSnodgrass Oops. Misunderstood there. The data contains singular daily entries. It's always 7 days behind today's date in terms of data availability.

@MarkSnodgrass Thanks for the link! So, would the 3-week rolling average prior to the last complete week look like this? (LAG(SUM(DATE_ADD(`dt`, INTERVAL (7- DAYOFWEEK(`dt`)) DAY)), 3) OVER (ORDER BY `dt`) + LAG(SUM(DATE_ADD(`dt`, INTERVAL (7- DAYOFWEEK(`dt`)) DAY)), 2) OVER (ORDER BY `dt`) + LAG(SUM(DATE_ADD(`dt`,…

Thank you @jaeW_at_Onyx for breaking that down for me!

That did it @RobSomers ! Thank you for your help on this. 😀