Forum Discussion

Lightning0114's avatar
Lightning0114
Qrew Cadet
6 years ago

Filter reports based on certain days after a date field

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!
Formulas and functions
reports and charts
  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    6 years ago
    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.
  • Lightning0114's avatar
    Lightning0114
    Qrew Cadet
    6 years ago
    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?


    • LauraThacker's avatar
      LauraThacker
      Qrew Captain
      6 years ago
      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.