8 Replies Latest reply on Sep 29, 2017 6:42 PM by Jussi Talvala

    Filtering out records that are not the max date within a month

    Jussi Talvala

      The data I'm working with takes a weekly snapshot, but is reported monthly. Each week the same data is snapshot and retained, so in the the database I see the same events duplicated multiple times. I want to display a report using only the last date within each month.

       

      The data set looks something like this:

       

      Production Run DateSnapshot Date (dd/mm/yyyy)Person IDClosed
      20170601/07/201710
      20170610/07/201710
      20170629/07/201710
      20170615/08/201711
      20170629/08/201711

       

      Before I do any reporting, I'd like to filter out all records except the last one in each month on the snapshot date. This data can span years so I need to account for that as well. Any advice? I tried doing max date calculated fields but having issues keeping it isolated to the specific combination of year and month.

       

      The data set should look like this at the end:

       

      Production Run DateSnapshot Date (dd/mm/yyyy)Person IDClosed
      20170629/07/201710
      20170629/08/201711