1 Reply Latest reply on Feb 21, 2013 5:47 AM by karenpeacock

    Cystal Reports formula Within last "X" months

    Rookie

      I am trying to write a formula to find Incidents logged within the last full 12 months from today's date.  So if I run a report in February it gives me the results from February 2012 - Janauary 2013.  I don't want to use the formula for the last full year as it gives me the whole of 2012 rather than February 2012 to end of January 2013.

       

      I would word it like the following example:-

       

      Is the {creation date{month}} = to {today's date{month}} - minus {six months} then

        formula = "Y"

       

      else

       

      formula = "No"

       

      Not sure how to do the date calculation against the pm_process.creationdate field.

       

      Any suggestions?

        • 1. Re: Cystal Reports formula Within last "X" months
          karenpeacock SupportEmployee

          Hi

           

          I did something similar-ish recently to bring back only those records which were in the current quarter by using the datepart function. 

           

          If (Totext ( DatePart ( 'q' , {@Creation Date To Date}) , '0') & 'Q' & Totext (Year ({@Creation Date To Date}), '0000')) =

          (Totext ( DatePart ( 'q' , CurrentDate) , '0') & 'Q' & Totext (Year (CurrentDate), '0000')) then 1 else 0

           

          The pm_process.creationdate field is a datetime so you might need to change it to just a date first this would be something like Date(pm_process.creationdate) or ToDate(pm_process.creationdate), think this changed between Crystal versions  The {@Creation Date To Date} my formula references was the formula I had used in order to get the data I was using into the format of a date.  I then restricted my report to only show me records where the formula returns a 1 but you could use Yes and No. 

           

          I think this method of getting data might slow down the performance of the report because I expect it would need to calculate the formula against each record to check whether it is needed or not.  For this reason, it might be a good idea to also have another criteria or a database view which only looks for records in the past 2 years (or something which makes sense to your data).

           

          To be honest I think I adapted a formula which I found via some internet searching, so I would recommend having a look for some examples using DatePart on the month.

           

          Best wishes

          Karen