New Year Filtering Issues by Week / Month

RocketMichael

My date filters are on the struggle bus in the new year y'all.

CASE when WEEK(`Week Date`) = Week(CURDATE())-1 then 'show' else 'hide' end = 'show'

The filter is simply returning this current week instead of the previous week. I assume this is because we are in Week 1, and the previous Week was 52.

What's a better formula for this that is solely focused on the week chronologically instead of the numeric representation?

(I know I am going to run into the same issue with months when I get to the next dashboard)

GrantSmith

Try something like this:

CASE WHEN (`Week Date` + INTERVAL (7-DAYOFWEEK(`Week Date`)) DAY) = (CURDATE() - INTERVAL (DAYOFWEEK(CURDATE)) DAY) THEN 'Last Week' ELSE 'NOT' END

The first part is is adding days to the week to get the last day in the week. The second half is calculating the end of the week based on the current date (for example Monday = 2 so it's subtracting 2 days to get to Saturday of last week)

RocketMichael

@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 filter tile.

So, all of the dates passing into this filter are Sundays (the start of the week for this data set).

GrantSmith

How are you utilizing the beast mode? Are you attempting to convert the value somewhere to a boolean?

Because you're using the first of the week you could simplify the beast mode to something like:

CASE WHEN `Week Date` = (CURDATE() - INTERVAL (DAYOFWEEK(CURDATE) + 6) DAY) THEN 'Last Week' ELSE 'NOT' END

RocketMichael

@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.

RocketMichael

@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 issue, but there was something weird going on there as I got errors if I used a new tile instead of editing an existing one.

** So I have one last remaining question- and that is, how do I apply this for monthly data?

• Current date format uses the first day of each month (example 2022-08-01)

Thank you for your time on this- it is greatly appreciated.

GrantSmith

You can use DAYOFMONTH instead of DAYOFWEEK

First of the current month:

CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY

RocketMichael

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

Is 12-1-21 the value of your `Month Date` column? Is this a date column or a string column in your dataset?

RocketMichael

@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.