Filter reports based on certain days after a date field

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
Hello. I want to filter a report that shows records where the field "Week2 Follow up Date"  is 7 days after "Week1 Follow up Date". I see there is a filter option (after the date in the field) but the goal is to show records that are not within 7 days from "Week1 Follow up Date". Any help would be great. Thanks!
Photo of Ma. Isabel Gumanid

Ma. Isabel Gumanid

  • 256 Points 250 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 6,396 Points 5k badge 2x thumb
You will need either an additional formula-field or an in-report Formula to achieve this because you actually want to compare two timestamp fields together based on the date of them being a specific time frame apart. 

If you need this formula value in more than 1 report; you should build a formula-checkbox.  Otherwise, in the report you have; add a custom-formula and use that in your Filtering section of the report.

If(ToDate([Week Two Follow-up Timestamp]) >= ToDate([Week One Follow-up Timestamp])+Days(7),true,false)

This will evaluate if the Week Two Follow-up Timestamp is equal to or greater than the Week One Follow-up Timestamp date plus 7 days.
Photo of Ma. Isabel Gumanid

Ma. Isabel Gumanid

  • 256 Points 250 badge 2x thumb
Thanks for the quick response. I need it only for one report.

1. Anyway, did you mean to say that I create another formula-checkbox field (say I name it Week1 and Week2 omparison) using this formula below then at the filter section of the report, use that field to set up something like this "Week1 and Week2 Comparison is equal to checked"? 

For formula checkbox field:
If(ToDate([Week Two Follow-up Timestamp]) >=ToDate([Week One Follow-up Timestamp])+Days(7),true,false)

2. Another thing, I don't need the new field (Week1 and Week2 comparison) to be shown on this report. Is this possible to use the field for filters but not showing it on the report?


Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 6,396 Points 5k badge 2x thumb
Since you only need this comparison to made for 1 report; then you can build a custom Report-Filter.  You can use anything in a filter without showing the field on a report. 
(Edited)
Photo of Ma. Isabel Gumanid

Ma. Isabel Gumanid

  • 256 Points 250 badge 2x thumb
Thank you! This should work fine.