SUBDate As Filter?

hfruecht · 2024-05-30T20:01:23+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Filters

Magic ETL

Accepted answers

All comments

david_cunningham

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

hfruecht

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

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