4 Replies Latest reply on Mar 4, 2014 3:55 AM by NickJaggers

    How do you find values in a date field during the previous 2 months

    NickJaggers

      Title

      How do you find values in a date field during the previous 2 months

      Post

           Hi,

            

           I have found a script which allows me to find records with a date in a Date field during the last month on the help pages, namley :

      (Month (DateField) = (Month(Status(CurrentDate)) - 1) and Year (DateField) = Year(Status(CurrentDate))) or (Month (DateField) = 12 and Year (DateField) = (Year(Status(CurrentDate)) - 1)) 

           .... but how do you find records with a date in the past 2 months and take in to account dates that fall into the previous year.

           Thanks. Nick

            

            

        • 1. Re: How do you find values in a date field during the previous 2 months
          philmodjunk

               Let ( [ T = Get ( CurrentDate ) ;
                         m = month ( T ) ;
                        Y   = year ( T ) ;
                        m1 = m - 2 + If ( m < 3 ; 12 ) ;
                        y1 = y - ( m < 3 )
                       ] ;
                        m1 & "/" & y1 & "..." & m & "/" & y
                      )

               will produce the needed criteria to use in a find from two months ago to the current month. This assumes that there are dates from the future in your table. You can use this expression if there are no dates greater than today's date in your table:

               ">" & m1 & "/" & y1

          • 2. Re: How do you find values in a date field during the previous 2 months
            NickJaggers

                 Thanks Phil,

                  

                 Not too sure how to combine this is to my calculation field, Sorry probably being really thick and got a major mental block as I'm not a developer, so any further help greatly appeciated with the actual calculation script. Here's what I need to do:

                  

                 In one of the table's fields I am entering  a date when the record was created [date entered], in another a quanity [work delivered] and then a calculation field [work delivered 2 months a go]. THere is then a summary filed to calculate the total of [work delievred 2 months a go] across all the records..

                 I need the calculation field to check the month and if the month was 2 months a go from the current month, use the value of the [worrk delivered]. I'm not interested in actual dates, just whether the month matches, It needs to take in to account work in the previous year if that's applicable.

                  

                 This is what is what I need it to look like with similar calculation fields to do the same for work delieverd this month and also last month. For example if the current month, i.e. month(get(currentdate)) is  Feb 2014, the following is expected:

                  

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                DATE ENTERED                     WORK DELIVERED                     

                                     WORK DELIVERED 2 MONTHS A GO

                                

                                     i.e. DEC 2013

                           
                                

                                     WORK DELIVERED LAST MONTH

                                

                                     i.e. JAN 2014

                           
                                

                                     WORK DELIVERED THIS MONTH

                                

                                     i.e. FEB 2014

                           
                                01/2/2014                     2                     0                     0                     2
                                5/1/2014                     3                     0                     3                     0
                                17/12/2013                     5                     5                     0                     0
                                15/11/2013                     1                     0                     0                     0

                  

                 The calculation script i'm using for work delivered this month for example is:

                 work delivered this month = If (Month(Get(CurrentDate)) = Month (Date Entered) and Year (Date Entered) = Year (Get(CurrentDate));Work Delivered;0)

                  

                 The script at the start of my post, that was supposed to calculate the Work Delivered Last Month, does not work, as it adds any results for the December of the previous year, so this needs a similar solution too to only include it if the current month happened to be january/.

                  

                 Many thanks,

                  

                 Nick

            • 3. Re: How do you find values in a date field during the previous 2 months
              philmodjunk

                   What I described would not be put in a calculation field. You wanted to find these records and this calculation produces the needed date range expression to use in a script to find records in that date range.

                   For examples of such scripted finds, see this thread: Scripted Find Examples

                   If you want to set up a relationship or a portal filter that limits to a specific date range, you are not technically "finding" records. It's possible to do that, but not with the calculation that I recommended in my first post.

              • 4. Re: How do you find values in a date field during the previous 2 months
                NickJaggers

                     Okay .... managed to come up with a solution thanks to the help of a colleague ...

                     In the calculation field, you can use the following expression:

                case(Month(Get(CurrentDate))>2 AND (Month(Date entered)=Month(Get(CurrentDate))-2) AND Year(Date entered)=Year(Get(CurrentDate));WorkDelivered;Month(Get(CurrentDate))<3 AND (Month(Date entered)=Month(Get(CurrentDate))+10) AND (Year(Date entered)= Year(Get(CurrentDate))-1);WorkDelivered;0)

                      

                If you wanted go back three months instead:

                case(Month(Get(CurrentDate))>3 AND (Month(Date entered)=Month(Get(CurrentDate))-3) AND Year(Date entered)=Year(Get(CurrentDate));WorkDelivered;Month(Get(CurrentDate))<4 AND (Month(Date entered)=Month(Get(CurrentDate))+9) AND (Year(Date entered)= Year(Get(CurrentDate))-1);WorkDelivered;0)

                      

                Thanks everyone for your help.