How do you build a filter for a date range?

Shana
Shana Member
in Magic ETL

I need to build out a forcasting dashboard for my purchasing team. They need to look at the last 3 months sales for some items and I was able to get a filter to give me that range in my ETL. However, the other metric they use for some items is a term called "forward 3 months". This means that they need the next three months but from last year. For example, if the current month is September 2023, the forward 3 months would be Oct. - Dec. 2022. I've tried several formulas but all have failed to produce any rows in the test feed I built directly off that filter.

Here's the current formula that I thought would be close but is not working.

(year(Date)=DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND month(Date)>=DATE_add(CURDATE(), INTERVAL 1 MONTH))

AND
(year(Date)=DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND month(Date)<=DATE_add(CURDATE(), INTERVAL 3 MONTH))

Tagged:

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    edited September 2023 Answer ✓

    CASE WHEN `DateValue` >= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 12 MONTH
    AND
    `DateValue` <= LAST_DAY(CURRENT_DATE()) - INTERVAL 9 MONTH THEN 'In' ELSE 'Out' END

    If I solved your problem, please select "yes" above

  • ColemenWilson
    ColemenWilson Coach
    edited September 2023 Answer ✓

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited September 2023 Answer ✓

    CASE WHEN `DateValue` >= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 12 MONTH
    AND
    `DateValue` <= LAST_DAY(CURRENT_DATE()) - INTERVAL 9 MONTH THEN 'In' ELSE 'Out' END

    If I solved your problem, please select "yes" above

  • ColemenWilson
    ColemenWilson Coach
    edited September 2023 Answer ✓

    If I solved your problem, please select "yes" above

  • Shana
    Shana Member

    This is perfect!!! It worked and filtered the dataset as I expected it to. Thank you so much!!!

Categories