3 Replies Latest reply on Mar 16, 2018 8:25 AM by swaroop.gantela

    Graphing records based on modified date

    James Moran

      I have data in the following format:

       

      Model          Status          Last Modification

      1122          Open               01/01/2004

      1122          Closed              01/01/2018

      1123          Open               01/01/2009

      1123          Pending          06/02/2010

      1123          Closed               04/02/2017

       

      I need to be able to count the number of distinct models (countd[Model]) by date. If there is no change in status, then the latest status should be applicable for the intervening period.

       

      The problem is that if I drag [Last Modification] as the date field, then in the example, the count for 'Open' models in 2005 will be zero...in fact, 2005-2008 (inclusive) do not appear.

       

      Any ideas how I could get around this (changing the database is problematic as there are a lot of records is would be necessary to add 1122 Open 01/02/2004, 1122 Open 01/03/2004 ...)

       

      Thanks,

       

      James