Dynamic Date Formulas

Thu, 01 Apr 2010 14:00:11 GMT

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!

Dynamic Date Formulas

Thu, 01 Apr 2010 14:26:42 GMT

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

Dynamic Date Formulas

Thu, 01 Apr 2010 15:14:48 GMT

thanks, but no dice.

the addmonths function does not work in there either.

are these functions converting the dates to text or numbers?

topic Dynamic Date Formulas in QlikView

Dynamic Date Formulas

Dynamic Date Formulas

Dynamic Date Formulas