Nov 10, 2023 02:28 AM
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:
Solved! Go to Solution.
Nov 10, 2023 03:54 AM
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)
Nov 10, 2023 03:54 AM
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)
Nov 10, 2023 04:15 AM
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!!