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
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/.
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.
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.