New Year Filtering Issues by Week / Month

RocketMichael
RocketMichael Member
in Magic ETL

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)

image.png


Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    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)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • RocketMichael
    RocketMichael Member

    @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
    GrantSmith Coach

    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
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • RocketMichael
    RocketMichael Member

    @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
    RocketMichael Member
    edited January 2023

    @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
    GrantSmith Coach

    You can use DAYOFMONTH instead of DAYOFWEEK

    First of the current month:

    CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • RocketMichael
    RocketMichael Member

    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
    GrantSmith Coach

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • RocketMichael
    RocketMichael Member
    edited January 2023

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

Categories