Forum Discussion

Qrew Member

3 years ago

Filter in Report using Time Period Filter

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))...

reports and charts

MarkShnier__You

Qrew Champion

no problem.

Just make a formula checkbox field called [Date is in the Current Period?]

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 Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

Qrew Member

3 years ago

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.

Here is my screenshot:

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

MarkShnier__You
Qrew Champion
3 years ago
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
  Qrew Member
  3 years ago
  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
    Qrew Champion
    3 years ago
    np, just make a new field called [Date is in the Previous Period?] with a formula of
    
    FirstDayOfPeriod(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 Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------