16 Replies Latest reply: Jul 11, 2018 9:21 AM by donato donato RSS

    Today() in set analysis with 7 rolling days

    donato donato

      Hello all,

      This should be an easy question but I'm having troubles with the Today() condition in my set analysis.

       

      I want to create a report that automatically updates itself every day and shows me the results of the last 7 days. In my report I want to show only the ID number with status "OK", type "OUT" and that have occurred in the last 7 days.


      The names of the fields are:

      ID number = ID_NUMBER

      Status = STATUS

      Type = TYPE

       

      I've tried several alternatives, such as

       

      only({$<STATUS={'OK'},TYPE={'OUT'},LAST_UPDATE={'>(today()-7)'}>}ID_NUMBER)

      only({$<STATUS={'OK'},TYPE={'OUT'},LAST_UPDATE={'>$(today()-7)'}>}ID_NUMBER)


      However, the table shows me no results.


      Whereas, if i try with a real date, the report is correct


      only({$<STATUS={'OK'},TYPE={'OUT'},LAST_UPDATE={'20/06/2018 00:00:00'}>}ID_NUMBER)


      I think there is a problem with the format of my data. Unfortunately, i cannot change their format.


      Here, an example of my database

          

      ID_NUMBERACQUISITION_DATELAST_UPDATESTATUSTYPE
      0000128/02/20183/2/2018 6:20OKOUT
      0000227/02/20183/2/2018 0:00OKOUT
      0000328/03/20183/31/2018 0:00NOOUT
      0000430/10/20171/18/2018 13:20NOIN
      0000528/03/20183/31/2018 0:00OKIN
      0000620/02/20182/23/2018 0:00NOOUT
      0000720/02/20182/22/2018 6:13OKIN
      0000819/06/20186/19/2018 16:27NOIN
      0000920/06/20186/23/2018 0:00NOOUT
      0001020/06/20186/23/2018 0:00NOIN
      0001121/06/20186/25/2018 4:03OKOUT
      0001229/06/20187/3/2018 4:28OKOUT

       

       

      Any idea on what might be wrong with Today()? This seems like a syntaxis issue, but I can't see what the mistake is.


      Thank you

      Don