Skip to main content

New to Qlik Sense

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

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
donatodonato
Contributor
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

16 Replies
donatodonato
Contributor
Contributor
Author

Sorry,  I cannot understand where to paste the Calendar script 😕

I know IF condition takes more time, I'm trying to use the set analysis with the Only condition but I have this problem with the date only with the set analysis and the only condition.

agigliotti
Partner - Master III
Partner - Master III

do you need time in your LAST_UPDATE field ?

I suggest you to apply the Floor function in your script.

ex. Floor(LAST_UPDATE) as LAST_UPDATE

then you can use:

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


I hope it helps.

donatodonato
Contributor
Contributor
Author

Thank you. Unfortunately, I cannot apply any function in the script and I cannot upload new data. I don't have any control on the data and I cannot apply changes in back end.

An external partner gives us the data and creates all the fields that I can use. Basically, I can only apply formulas or set analysis to these field but i cannot change any of them or create new ones.

Therefore, I need a formula that shows me the right results 😕

Thank you very much.

Hope you guys can give me some insights

zebhashmi
Specialist
Specialist

this one by Andrea should work

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

you can try

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

donatodonato
Contributor
Contributor
Author

Thank you, I tried this solution but the report  keeps me showing all the results, not only the results of the previous 7 days.

Thanks

agigliotti
Partner - Master III
Partner - Master III

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 )

donatodonato
Contributor
Contributor
Author

It works, I just changed the condition a bit (in bold)

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


Thank you very much!