Discussions

Expand all | Collapse all

Filter reports based on certain days after a date field

  • 1.  Filter reports based on certain days after a date field

    Posted 07-16-2019 15:52
    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!


  • 2.  RE: Filter reports based on certain days after a date field

    Posted 07-16-2019 16:04
    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.


  • 3.  RE: Filter reports based on certain days after a date field

    Posted 07-16-2019 16:33
    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?




  • 4.  RE: Filter reports based on certain days after a date field

    Posted 07-16-2019 16:54
    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. 


  • 5.  RE: Filter reports based on certain days after a date field

    Posted 07-16-2019 19:07
    Thank you! This should work fine.