5 Replies Latest reply: Jun 3, 2011 12:51 AM by narrireddy

# Date in set analysis

Hi Everyone Special,

I have two date related question.

A. I have to compare a Modified Date with less than or equal to Today's date.

In if...else statement it works fine for me if I write Modified_Date<=Today()

But it doesn't seem to work in set analysis if I write Modified_Date = {"<=\$(=Today())"} or Modified_Date={"<=Today()"} .Neither of them works.

B. In the second case Modified date is actually in Julian date. So in order to compare with Today's date I need it to be back in normal calendar format.

Hence in if...else statement it works fine if I write Date(Modified_Date)<=Today(), but no luck when I use set analysis as Date(Modified_Date)={"<=Today()"}.

Thanks for any suggestion,

Regards,

Bikash

• ###### Date in set analysis

Hi, for both cases, I prefer to use dates as integers since set analysis can get lost with different format.

On the script level I would create an additional field as an pure integer as:

num(Modified_Date) as nModified_Date

from / Resident (... your regular code)

On set analysis, your expressions will look like this:

sum (  { < nModified_Date = {'<=num(today())'} >} Value)

Hope this helps,

Erich

• ###### Date in set analysis

The basic thing is to make sure your field and the values of the set are in the exact same format.  Set analysis basically does a text comparison.  One way to be sure you have the exact same format is to convert them both to numbers as Erich says.  Another is to use the date() function inside of the set analysis to format the date exactly as your date field is formatted.

• ###### Date in set analysis

Thank you Erich for the solution.

• ###### Date in set analysis

John, thanks a lot for teaching the basic concept behind the solution. Loved it.

• ###### Date in set analysis

Hi, for both cases, I prefer to use dates as integers since set analysis can get lost with different format.

On the script level I would create an additional field as an pure integer as: