topic Date in set analysis in QlikView

Date in set analysis

Thu, 02 Jun 2011 18:06:15 GMT

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

erichshiino — Thu, 02 Jun 2011 22:01:51 GMT

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:

load (... your regular code) ,

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

johnw — Thu, 02 Jun 2011 23:21:47 GMT

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

Fri, 03 Jun 2011 00:17:04 GMT

Thank you Erich for the solution.

Date in set analysis

Fri, 03 Jun 2011 00:17:39 GMT

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

Date in set analysis

Fri, 03 Jun 2011 04:51:16 GMT

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:

load (... your regular code) ,

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)