Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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 )
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
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 )
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!