Skip to main content
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Today() in set analysis with 7 rolling days

Hello all,

This should be an easy question but I'm having troubles with the Today() condition in my set analysis.

I want to create a report that automatically updates itself every day and shows me the results of the last 7 days. In my report I want to show only the ID number with status "OK", type "OUT" and that have occurred in the last 7 days.

The names of the fields are:

ID number = ID_NUMBER

Status = STATUS

Type = TYPE

I've tried several alternatives, such as



However, the table shows me no results.

Whereas, if i try with a real date, the report is correct

only({$<STATUS={'OK'},TYPE={'OUT'},LAST_UPDATE={'20/06/2018 00:00:00'}>}ID_NUMBER)

I think there is a problem with the format of my data. Unfortunately, i cannot change their format.

Here, an example of my database


0000128/02/20183/2/2018 6:20OKOUT
0000227/02/20183/2/2018 0:00OKOUT
0000328/03/20183/31/2018 0:00NOOUT
0000430/10/20171/18/2018 13:20NOIN
0000528/03/20183/31/2018 0:00OKIN
0000620/02/20182/23/2018 0:00NOOUT
0000720/02/20182/22/2018 6:13OKIN
0000819/06/20186/19/2018 16:27NOIN
0000920/06/20186/23/2018 0:00NOOUT
0001020/06/20186/23/2018 0:00NOIN
0001121/06/20186/25/2018 4:03OKOUT
0001229/06/20187/3/2018 4:28OKOUT

Any idea on what might be wrong with Today()? This seems like a syntaxis issue, but I can't see what the mistake is.

Thank you


1 Solution

Accepted Solutions
Partner - Champion
Partner - Champion

if your LAST_UPDATE timestamp field is in MM/DD/YYYY hh:mm format you can try:

only( {< STATUS={'OK'}, TYPE={'OUT'}, LAST_UPDATE = {">$(=Date(Today()-7,'MM/DD/YYYY hh:mm'))"} >} ID_NUMBER )

View solution in original post

16 Replies
Creator II
Creator II

Try wrapping it with Date():


Not applicable

Thank you very much. I tried but it doesn't work


since when do we use single quotes for expressions that need to be evaluated?

Quotes in Set Analysis

and as you need to evaluate == > $(= ...)


Not applicable

Hi, it doesn't work even with this condition. I think there's a problem with the data format. The report still shows no results. Thank you

Master II
Master II

Might be the date format is the issue?

Use these 2 in back end...

//preceding load//


trim(date(LAST_UPDATE_New ,'YYYYMMDD')) as [DateID];

//Original load//


date(floor(timestamp#(LAST_UPDATE ,'M/DD/YYYY h:mm')),'M/DD/YYYY') as LAST_UPDATE_New

In front end and use

only({$<DateID = {  $(=Max(DateID) - 7)}>}ID_NUMBER)

Not applicable

Hi, thank you for the reply. I'm new to Qlik and I don't know how to change it in back end? Any suggestion? Thank you

Not applicable

I tried to use the IF function .

I used this formula

if(TYPE='OUT' and STATUS='OK' and date(LAST_UPDATE)>=(today()-7), count(ID_NUMBER),null())

It used to work at the beginning, but after some days I had "Error: Calculation Timed out" (maybe because the rows of data increased dramatically).

Anyone has an idea why it works with the IF but it's not working in the set analysis with the only?

Since it used to work with the IF function, is there a way to increase the calculation time?

Thank you

Master II
Master II

paste your Calendar script here or if you are getting dates from your Dimension or Fact paste that.

And for Time out error, yes it is due to large number of rows

and using IF condition would take more time... try using Match or Set analysis

Not applicable

If I use this condition,


I can see that date() and today() functions are not evaluated when I use double quotes.


Whereas, if i delete the double quotes date() and today() functions are evalutated (they are in blue), like in the following picture.

Capture 2.JPG

Do you think I am doing something wrong with the formula?

Thank you