Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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

1 Solution

Accepted Solutions
erich_shiino
Honored Contributor

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:

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

5 Replies
erich_shiino
Honored Contributor

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:

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

MVP
MVP

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.

Not applicable

Date in set analysis

Thank you Erich for the solution.

Not applicable

Date in set analysis

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

Not applicable

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:

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)

Community Browser