Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
erichshiino
Partner - Master
Partner - Master

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

View solution in original post

5 Replies
erichshiino
Partner - Master
Partner - Master

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

johnw
Champion III
Champion III

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
Author

Thank you Erich for the solution.

Not applicable
Author

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

Not applicable
Author

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)