Reporting Previous Day Information

[MikeyC]

Hi there,

Let's say today is April 27th, 2007.  I run daily reports that run for the previous business day data.  In the query I have entered in all of the relevant parameters and end it with the date parameter.  It ends with "... Effective Date = 2007-04-26".  Every business day I must run these reports and I manually go into the query and change it to the previous day.  There is the scheduler built in and it works.  However, I am not sure how to enter a previous day calculation.  Would you please be able to help? 

Right now, just getting the previous day would be amazing, then I'll start trying to figure out previous business day (the system probably won't be able to figure out holidays)

Please let me know
Thx in Advance Mike

[COGNOiSe administrator]

Use _add_days(current_date, -1)

[Alacran]

And if you wish a previous bussines day (Mon - Fri) you can try this :

      if ( dayofweek (now()) = 2 ) then ( add-days (today(), -3)) else (adddays (today(), -1))

[MikeyC]

Hi Thanks for your help,

I tried running as mentioned.  The Effective Date has a 'date' parameter in the format of 2007-05-01.  I am entering in the following Query (also tried as calculation in Report Column tab):

Effective Date = (add-days (curdate(),-1).   - Impromptu: The software kept on running with no output.  (Also attempted to enclose (-1) with brackets to see if it was an sign issue, no output)


I created a new column Add Days to see what was produced while still leaving in "Effective date = '2007-05-01'".  In the Effective Date column "05/01/2007" (as always) is outputted, and the Add Days column "05/01/2007 12:00am" - not sure if that has anything to do with it.    When I remove "Effective Date = 2007-05-01" from the query, the report just keeps on running with no output.

I'm having issues figuring out why it won't run :S

Note: There is no current_date funtion, hoping there is no difference between this and curdate.   

[Alacran]

I think there is no such a  current_date function, use today() function instead, as follow:

Efective Date = if ( dayofweek (now()) = 2 ) then ( add-days (today(), -3)) else (adddays (today(), -1))

I hope this help ...

[COGNOiSe administrator]

You also need to convert it to same format on both sides of the comparison.

[MikeyC]

Thanks again,

Still having issues.  I am able to run the query with the previous day, but the output is never ending rows: previous day, previous day -1, ... previous day -(n).

Background: The information is based on trade dates.  When removing an exact date from the query, the output would be all the trade dates since inception.  By using the formula adddays(curdate, -1), the output is still all the trade dates since inception except the starting reference point is the previous date.  Moreover, I take the current date -1, which is generated in a new row [previous day].  Then I take the previous date and change it to datetime to date as this is needed to remove the time.

1.  I have attempted to create a formula that states (column 1) previous day > (column 2) previous two days - Just keeps on running with no output
2. Effective Date = Previous date - keeps on running with no output

The report seems to be working as (previous date) as the start date, so it reports every date since inception.  The issue is how to get it to have an endpoint to stop running of greater than (previous two days)