Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

donatodonato
New Contributor

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

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_NUMBERACQUISITION_DATELAST_UPDATESTATUSTYPE
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

Don

1 Solution

Accepted Solutions
agigliotti
Honored Contributor II

Re: Today() in set analysis with 7 rolling days

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 )

16 Replies
williamfuu
Contributor II

Re: Today() in set analysis with 7 rolling days

Try wrapping it with Date():

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

donatodonato
New Contributor

Re: Today() in set analysis with 7 rolling days

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

mikaelsc
Contributor III

Re: Today() in set analysis with 7 rolling days

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])

donatodonato
New Contributor

Re: Today() in set analysis with 7 rolling days

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

arvind654
Honored Contributor II

Re: Today() in set analysis with 7 rolling days

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)

donatodonato
New Contributor

Re: Today() in set analysis with 7 rolling days

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

donatodonato
New Contributor

Re: Today() in set analysis with 7 rolling days

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

arvind654
Honored Contributor II

Re: Today() in set analysis with 7 rolling days

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

donatodonato
New Contributor

Re: Today() in set analysis with 7 rolling days

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.

Capture.JPG

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