0

Syntax for filtering Dates older than from today

I'm trying to find the correct syntax for filtering based on dates older than 5 working days before today.

 

This is what I was envisioning, but it's a little off in regards to syntax.

workingdays(last(Chronos.'Date + Time')) + 5 < today()

4 replies

null
    • Fred
    • 7 mths ago
    • Reported - view

    Ninox is really good with days. The thing you have to watch out for is you have a date/time field so you have to convert it to date only data with the date() command.

    I'm not sure where you got workingdays but there is a workdays() command. It would look something like this:

    workdays(last(Chronos.date('Date + Time')),today())

    This will return a number. If I remember your DB, this formula would appear in a parent record.

    You mention filtering but I'm not sure what you want to filter. You can use it to filter a reference field like:

    Chronos[workdays(date('Date + Time'),today())>=5]

    Here we are filtering Chronos by using the workdays() command we ask Ninox to find the number of workdays between the date of the 'Date + Time' field and today and only keep the ones that return a number that is greater than or equal to 5 (dates older than 5 working days before today).

      • Dave_Irving
      • 7 mths ago
      • Reported - view

       Thanks Fred, you got me there.  Sorry about the "workingdays" above, that was just me typing the code fast in the forums without proofreading it.  Since I have the view in my parent table, I didn't want to use the Chronos. table tag as that then would return all chrono records that are over 5 days long.  Instead, I have a 'Last Activity Date' field in my parent table that is already providing the date of the last record in chronos for that linked client.  So, I tweaked the code above to just go off of that field.

      workdays(date('Last Activity Date'), today()) >= 5
      • John_Halls
      • 7 mths ago
      • Reported - view

       Hi Dave. The Chronos Fred mentions is the relationship field rather than the table. It will only show related records older than 5 working days.

      Regards John

      • Fred
      • 7 mths ago
      • Reported - view

      If I remember correctly, 'Last Activity Date' is already in date format so you don't need the date() command anymore. Glad to see you taking the ideas learned in the forums and modifying it to your situation.

Login to reply

Content aside

  • Status Answered
  • 7 mths agoLast active
  • 4Replies
  • 69Views
  • 3 Following