Bringing One Week Of Data

[Kristie_Pires]

Hello there,

I want to automatically filter my data from beginning to end of the prior week (Monday to Friday). Right now I am using the range in the filter tool, but would like to have it done automatically.

I would appreciate any help! Thank you :) 

[IraWatt]

Hey @Kristie_Pires,

Here is one way of doing it:

It checks the week and year number is the same as the week before you run it. And also checks its not a Saturday or Sunday. 

Any questions or issues please ask :)
HTH!
Ira

[PhilipMannering]

I think if you put this expression in the Filter Tool it will do what you need,

datetimeformat(datetimenow(), '%U') = datetimeformat([Date], '%U')

It filters to the current week.

[IraWatt · Accepted solution]

@Kristie_Pires  building on @PhilipMannering answer you could also put this in a formula tool for the same effect :

datetimeformat(DateTimeAdd(datetimenow(),-7,"days"), '%y %U') = datetimeformat([Date], '%y %U') AND datetimeformat([Date], '%A') != "Saturday" AND datetimeformat([Date], '%A') !="Sunday"

This would check against the year (important if you have more then 1 year of data) and also the day of the week.

[PhilipMannering]

I'm assuming if the data runs from Monday to Friday you can disregard weekends.

@IraWatt Good catch with adding the year.

[Kristie_Pires]

Thank you @IraWatt  and @PhilipMannering for helping me out :) 

@IraWatt your solution got me the previous week's data that I was looking for! 

[IraWatt]

No worries @Kristie_Pires glad it worked 😄

[Kristie_Pires]

@IraWatt Today I noticed that the report wouldn't bring July 1st data. I think the custom filter works great only if I am not bringing the first of the month.

I want to bring data from June 27 to July 1st (last week in the calendar). Do you know how I could achieve that or what in the formula you gave me stops that from happening?

Thank you!