Today() in set analysis with 7 rolling days

[Anonymous]

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

only({$<STATUS={'OK'},TYPE={'OUT'},LAST_UPDATE={'>(today()-7)'}>}ID_NUMBER)

only({$<STATUS={'OK'},TYPE={'OUT'},LAST_UPDATE={'>$(today()-7)'}>}ID_NUMBER)

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

ID_NUMBER	ACQUISITION_DATE	LAST_UPDATE	STATUS	TYPE
00001	28/02/2018	3/2/2018 6:20	OK	OUT
00002	27/02/2018	3/2/2018 0:00	OK	OUT
00003	28/03/2018	3/31/2018 0:00	NO	OUT
00004	30/10/2017	1/18/2018 13:20	NO	IN
00005	28/03/2018	3/31/2018 0:00	OK	IN
00006	20/02/2018	2/23/2018 0:00	NO	OUT
00007	20/02/2018	2/22/2018 6:13	OK	IN
00008	19/06/2018	6/19/2018 16:27	NO	IN
00009	20/06/2018	6/23/2018 0:00	NO	OUT
00010	20/06/2018	6/23/2018 0:00	NO	IN
00011	21/06/2018	6/25/2018 4:03	OK	OUT
00012	29/06/2018	7/3/2018 4:28	OK	OUT

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

Don

[william_fu]

Try wrapping it with Date():

LAST_UPDATE={'>$(Date(today()-7))'}

[Anonymous]

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

[mikaelsc]

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

Quotes in Set Analysis

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

Sum({<LAST_UPDATE={">$(=date(today()-30))"}>}[WhaterFieldYouSum])

[Anonymous]

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

[MK9885]

Might be the date format is the issue?

Use these 2 in back end...

//preceding load//

load*,

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

//Original load//

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)

[Anonymous]

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

[Anonymous]

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

[MK9885]

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

[Anonymous]

If I use this condition,

Sum({<LAST_UPDATE={">$(=date(today()-30))"}>}[WhaterFieldYouSum])

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.

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

Thank you