LAST WEEK EVENTS FORMULA (INCLUDE DIFERENT YEARS)

[adriaurora]

Hello! Thanks in advance to everyone.

I'm wondering how can I create a formula which allows me to know if an event has ocurred the past week, but I don't want the event of the past 7 days, I really need the past week. For example: if today is wednesday I want to know what had happened between the monday and the sunday of the past week.

I have done it by this formula: 

IF(DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Europe/Madrid'), 'W')-1={EVENT WEEK}, 1, 0), but the problem with this formula is that it is not going to work the first week of the year, because, obviously, that is the 1st week and the last week is 54 so the formula is not going to show the "1" which filters the view that I've created with that condition.

I dn't know if I've explained well. My english is not as fluent as it should be. Again, thanks in advance.

[Sho · Accepted solution]

Hi @adriaurora ,

You may use the Dateadd function to calculate dates.

IF(DATETIME_FORMAT(SET_TIMEZONE(DATEADD(NOW(), -1, "weeks"), 'Europe/Madrid'), 'W')={EVENT WEEK}, 1, 0)

[adriaurora]

Thanks @Sho!!!!

Just another question I've just wondered, if the 1st day of the year it's on Wednesday, for example, is there a way to tell the database that I want the entire week block (from Monday to Sunday)?

Just to give you a global vision of what I'm trying, I've made an automation that it's triggered every Thursday which sends by email the events that have happened between the last Thursday (past week) and Wednesday (actual week). I don't want to use the past 7 days filter because it doesn't work the way I want (Because it takes into account the hours and I need complete days) and because if that Thursday is not a workday I can manually trigger the automation on Wednesday and it sends me the same period of time (from Thursday to Wednesday).

OR(AND({weekday} >= 1, {weekday} <= 3, {actual week} = 1),AND({weekday} >= 4, {weekday} <= 6, {last week} = 1),AND({weekday} = 0, {last week} = 1))

The last week field is going to be the one you have proposed:

IF(DATETIME_FORMAT(SET_TIMEZONE(DATEADD(NOW(), -1, "weeks"), 'Europe/Madrid'), 'W')={EVENT WEEK}, 1, 0)

 Thank you so much again!!