Dynamic Date Formulas

[#1]

Hoping someone can help me, as I am having some difficulties getting QV to recognize dynamic date formulas.

Essentially, I would like QV to calculate some data using only the maximum date and each of the prior 2 months. For example, this month, I want to get data as of 12/1/2009, 11/1/2009, and 10/1/2009 by only filtering on those dates. While next month, I want to get data as of 3/1/2010, 2/1/2010, 1/1/2010 by only filtering those dates.

I'm trying to get the formula in QV to recognize the maximum date, and calculate the data based upon that max date in addition to each of the prior months.

If I use the simple formula of "max(CYCLE_DATE)", QV will give me the max cycle date, but when I try to encompass it into a calculation formula, I don't get any results.

Here is my formula:

count (if((CYCLE_DATE = (monthstart (NUM(year(today()))&'-'& NUM(month(Max (CYCLE_DATE))-1)& '-01' ))) AND ([AGE] = '30'),(PEOPLE)))

conversely, if I replaced "max(CYCLE_DATE)" with "today()" or "today()-1", then I do get my results. Problem is, I don't want the formula to look at today's date, I want it to look at the max date of the dates I filtered.

thanks!

[#2]

Hi,

see the attached example to get some ideas to achieve your goal.

Please spent a main emphasis on the field format (number or character).

Good luck!

Rainer

[#3]

thanks, but no dice.

the addmonths function does not work in there either.

are these functions converting the dates to text or numbers?