Filter in Report using Time Period Filter

Question

Hi! Our company has two week time periods. Thanks to this community, I was able to figure out how to create a simple Time Period Formula as follows: FirstDayOfPeriod([Date], Weeks(2), Date(2021,5,19)), but then when I use it to try and filter in the report, I am struggling.

Right now, I just put in the start date of the period and the end date of the period (see below), but this would, of course, require us to go into the report every new pay period and adjust the dates. How do I automate the report so that it always pulls up the current time period?

Thank you in advance!

Robin

------------------------------
Robin Wosje
------------------------------

markshnier__you · Answer

no problem.Just make a formula checkbox field called [Date is in the Current Period?]&nbsp;FirstDayOfPeriod(Today(), Weeks(2), Date(2021,5,19)) = FirstDayOfPeriod([Date], Weeks(2), Date(2021,5,19))In other words if the first day of the period of today matches the first day of the period of the particular date field then the date field must be in the same time period as today.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

robinwosje · Answer

I think I did it! THANK YOU!!I guess my only question is that QB will continue to count 2 weeks (since it is a period formula) for the next pay period, right? The 5/19/2021 date is just where it starts, but it will keep updating as we get into the new 2 week period.&nbsp;Here is my screenshot:------------------------------Robin Wosje------------------------------

markshnier__you · Answer

Yes, it will continue to work as time marches on and we fall into the Next "current" Payroll period.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

robinwosje · Answer

Ugh. The report worked great. However, what I didn't quite think through was that when we got to the first day of the new period, the report is empty. Of course, right?! Well, if someone wants to see what the entries are to make sure all of the time entries are in for the previous time period, I need to show them something. So, I kept the report at the top, but added a new report below it that shows the previous time period. The problem is that I don't know how to write that formula. So, instead (thinking I am really, really smart), I used the QB filter and said, show me dates in the last 9 days. It worked great, right? Well, the day after the period closed, it worked great, but of course, now it is repeating, because the last 9 days is now partially in the old time period and partially in the new time period. Man, I didn't think that through. So, my question is if there is a way to write a formula to make it report the previous time period which I can have below the current time period in case anyone needs to see previous entries (just one period prior).------------------------------Robin Wosje------------------------------

markshnier__you · Answer

np, just make a new field called &nbsp;[Date is in the Previous Period?] with a formula ofFirstDayOfPeriod(Today()-Weeks(2), Weeks(2), Date(2021,5,19)) = FirstDayOfPeriod([Date], Weeks(2), Date(2021,5,19))In other words if the first day of the period matches the first day of the period of two weeks ago from topay, then true.You can either make two reports or else put records on the report for where ANY of those checkbox fields being true and offer the user dynamic filters to choose their period.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

Filter in Report using Time Period Filter

6 Replies

Related Content

Loan database - Interest period

Embedded Grid Edit Report Pre-filtered Conditional Dropdown

Trying to create a report with an ask users filter, but with a multiselect

Is there a limit to number of filters on a dashboard

Report Formulas