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

[Anonymous]

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]

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.

[Anonymous]

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]

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 )

[Anonymous]

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 · Accepted solution]

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 )

[Anonymous]

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!