Filtering by Date (Last 7 days) while editing data extension filter

Question

My data extension has a date field stamped at a certain point in a journey and I need to filter it weekly to see who has been stamped in the last 7 days. I have tried a number of ways to configure the filter for it and so far only using "after (specified date) seems to be working. I want to automate this and get the right results. Can someone please advise on how to configure the date filter to show me those stamped with a date that is in the "last 7 days"?

Answer 1

There are two ways to do this with a rolling date:

---

USING FILTERS

This is by far the easiest way to do it and in most use cases is likely the best option. I know most people show a heavy preference towards SQL, but unless you are looking at an extreme volume, utilizing a filter here is likely to be easier to maintain and more performant. It does limit customization some though.

1. Create a filter with today - 7 days in a Filter Definition

2. Then create a Filter Activity inside of Automation Studio using this filter definition.

3. This will force you to create a new DE as the 'results' DE.

4. Pop this into a scheduled automation to continually run

5. Use the created 'results' DE for a view of the last 7 days of dates from your JB DE.

---

USING SQL

SQL is the preferred language for any ETL or data manipulation in SFMC. That being said, as stated above, it is not always the right answer - but sometimes just because 'best practice' is one thing, does not mean that for you it is what is the best solution. To that extent, I also wanted to share a SQL solution.

SELECT SubscriberKey,
UpdateDate,
MyField1,
MyField2
FROM [myDE]
WHERE CONVERT(date, UpdateDate) >= CONVERT(date,DATEADD(d,-7,getDate())

(similar to what @CasparHarmer commented)

1. We would use the CONVERT() to remove the time aspect as that is not important to your inquiry.
2. We use getDate() to get todays datetime
3. We would use DATEADD() to change the datetime to 7 days ago
4. We then compare the date from the DE to see if it is larger then or equal to the date 7 days ago.

---

As to which one you want to choose, it really is up to you. Both work really well and are viable solutions, so it really depends on your context and preference!