Joined: 06 May 2009
Location: United States
Online Status: Offline
Posts: 3
Topic: Last Quarter Function Posted: 06 May 2009 at 1:07pm
I have a report that I need to hardcode the date range. This report will be run by the user any date but it will give them the data included only on the previous quarter, EX:
1st quarter (1/1/09 - 3/31/09) can be printed any date before the 2nd quarter (4/1/09 - 6/30/09) the same with the other quarters.
I looked on the Predefined Date Range Constants on CR but I couldn't find a constant for Last Quarter.
I found on the archives a formula this formula:
{Incident_Date} in dateserial(year(DateAdd ("q", -1, currentdate)), (datepart("q",DateAdd ("q", -1, currentdate))*3)-2,1) to currentdate - day(currentdate), but when I include it on my report, the beginning date is OK but the ending date is not March 31st.
How can I modify this to formula to just provide me with the previous quarter no matter when the report is run.
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Posted: 07 May 2009 at 7:11am
OK, you are on a calendar year, way easier than some fiscal years.
Why not use something like:
if now in CDate(year(now), 1, 1) to CDate(year(now), 3,31) then
...
else
if now in CDate(year(now), 4, 1) to CDate(year(now), 6,31) then
...
I don't know this for a filter or what, but this should work...when I tried the above formula, it just subtracted 3 months from the current date (probably, not what you wanted)
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 07 May 2009 at 7:25am
I am sure there is a more elegant solution but this should also work:
if datepart("q",currentdate)=1 then datepart("q",{table.date})=4 and datepart("yyyy",{table.date})=datepart("yyyy",dateadd("yyyy",-1,currentdate)) else datepart("q",{table.date})=datepart("q",dateadd("q",-1,currentdate)) and datepart("yyyy",{table.date})=datepart("yyyy",currentdate)
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
