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: 
CreepyCatLady
Creator
Creator

Today()-90 giving inaccurate results

I have an app where I need a column to return "No" for items older than 90 days. I am using the following to create this:

if(LastConnectionTime>=Today()-90, 'No', 'Yes')

Result:

 

CreepyCatLady_2-1618420949084.png

It is working, but it is inaccurate. It is returning "Yes" for items newer than September 2020. As of the posting of this question, 90 days prior to today is January 14, 2021. 

I'm also a bit confused about why I need to use ">" and not "<", but when I use "<" it returns "No" for current date through Sept 2020 and "Yes" for dates Sept 2020 and prior, so I guess ">" is correct? At this point, I am very confused. 

 

 

1 Solution

Accepted Solutions
andykrst
Contributor III
Contributor III

maybe u need to convert LastConnectionTime to datetime 1st using timestamp#(),

if(num(timestamp#(LastConnectionTime,'M/D/YYYY h:m:s tt'))>=num(Today()-90), 'No', 'Yes')

 

hope this help, 

if not, try to using Trim() after make that field as num.

View solution in original post

5 Replies
andykrst
Contributor III
Contributor III

try to format your date time field to number maybe could help,

if(num(LastConnectionTime)>=num(Today()-90), 'No', 'Yes')

 

MayilVahanan

HI @CreepyCatLady 

Might be, ur field is in text format? 

If not, try with Floor() function in both side

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
CreepyCatLady
Creator
Creator
Author

@MayilVahanan , @andykrst  When I tried 

if(num(LastConnectionTime)>=num(Today()-90), 'No', 'Yes')

the result is just all "Yes". So unfortunately that is not the issue.

andykrst
Contributor III
Contributor III

maybe u need to convert LastConnectionTime to datetime 1st using timestamp#(),

if(num(timestamp#(LastConnectionTime,'M/D/YYYY h:m:s tt'))>=num(Today()-90), 'No', 'Yes')

 

hope this help, 

if not, try to using Trim() after make that field as num.

CreepyCatLady
Creator
Creator
Author

That worked! Thank you so much.