Hello, I need help on creating a

[kevnatajax]

Hello,

I need help on creating a grand total of annual premium minus 3 months from current month. This is called a 3 month off set number. Does anybody know how to create this formula?

Thank you very much for being there for me.

Renata

 

[synapsevampire]

If you want to limit rows to starting from 3 months back, then:

Use the dateadd function to return the date(s) of interest:

So if you only want data up to the start of the previous 3 months date, add something akin to this to the record selection criteria:

date(year(dateadd("m",-3,currentdatedate)),month(dateadd("m",-3,currentdate)),1)

This would return 6/1/2002 as the date for today, so you can add the following to the record selection criteria to filter only those rows:

{MyTable.MyDate} < date(year(dateadd(&quot;m&quot;,-3,currentdatedate)),month(dateadd(&quot;m&quot;,-3,currentdate)),1)

You can substitute however many months back by changing the -3.

Note that if your database field is a datetime, then you might want to use datetime as in:

datetime(year(dateadd(&quot;m&quot;,-3,currentdate)),month(dateadd(&quot;m&quot;,-3,currentdate)),1,0,0,0)

Now you can use conventional sums or running totals to supply numbers.

If you want the grand total minus the grand total for the past 3 months, then you should return all rows, and create a running total which has an evaluate clause which creates a filter criteria based on the aforementioned means of limiting data using the dateadd function.

Hope this is what you were after, or at least points you in the right direction.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com