Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
CreepyCatLady
Contributor III
Contributor III

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
CreepyCatLady
Contributor III
Contributor III
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.

View solution in original post

CreepyCatLady
Contributor III
Contributor III
Author

That worked! Thank you so much.