SUBDate As Filter?

hfruecht
hfruecht Member
in Magic ETL

Hi,

Can I use SubDate to filter a dataset within Magic ETL? We import our leadership team monthly. However, there are certain reports where I need the end of quarter leadership team instead of the most recent leadership team. Right now, I have the quarter end date hardcoded, but I would like to have a dynamic function.

Current: LeadershipDate = Date('2024-03-31')

Preferred: LeadershipDate = SubDate(CURRENT_DATE(),INTERVAL 1 Quarter)

I have the Leadership file > Filter Tile > Left Outer Join Tile add a Leadership flag to the main report.

Thank you!

Tagged:

Answers

  • david_cunningham
    david_cunningham Coach
    edited May 30

    @hfruecht

    The function you're looking for is DATE_SUB. You can use this, but you'll want to think through your filter. For example, do you have daily data, do you want to compare to the last day of of the month for the given quarter? If your data is updated monthly, then what is the date of that import - maybe it's better to compare month/year for the given quarter, etc. If you automate, you'll need to make sure your methodology is dialed in and behaving as expected.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • hfruecht
    hfruecht Member

    Thanks @david_cunningham

    The data is imported monthly, eventually it will be automated.

    Previously, the data was only imported quarterly and I had the data tile set to filter by batch. It would pull data processed in the last 1 month. This report is generally run once a quarter so it worked but I needed to make sure the timing was correct. Then, due to an issue, I needed to replace the entire table which showed me that using Filter by Batch wasn't the best solution.

    I'll play with the month/year filters.

  • hfruecht
    hfruecht Member

    I added a formula step to create a filter flag and then ran the leadership through the filter tile. This appears to work but will need to test and watch as the months change.

    CASE
    WHEN MONTH(TODAY()) IN (1, 4, 7, 10) AND LeadershipDate= SUBDATE(TODAY(),INTERVAL 1 MONTH) THEN 'Previous Quarter'
    WHEN MONTH(TODAY()) IN (2, 5, 8, 11) AND LeadershipDate = SUBDATE(TODAY(),INTERVAL 2 MONTH) THEN 'Previous Quarter'
    WHEN MONTH(TODAY()) IN (3, 6, 9, 12) AND LeadershipDate = SUBDATE(TODAY(),INTERVAL 3 MONTH) THEN 'Previous Quarter'

    END

    Filter Rule = Previous Quarter

Categories