asked on

Rolling Quarterly data - current 4 quarters

I posted this question about getting a rolling quarterly data query where only the current 4 quarters are showing and my numbers are still off:
https://www.experts-exchange.com/questions/21408454/Quarterly-Data.html

The query has this:
SELECT Format([date_keyed],"yyyy" & "-" & "q") AS Quarter
and this in hopes to show the rolling current 4 quarters
(((date_keyed)>=((Date()-Month(Date())+1)-300))

so as of today only these should show: 2004 3, 2004 4, 2005 1, 2005 2 but it's cutting some numbers due to this
(((date_keyed)>=((Date()-Month(Date())+1)-300))---if I increase it to 365 I will show 5 quarters...and I only want the current 4 but it should be rolling. Is that possible with quarterly data?

jkorz

you should never just subtract from a date, use the DateAdd function here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctDateAdd.asp

unless this is for sql server in which case you would want this one (select dateadd function on left):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp

Nestorio

Try,

date_keyed >= dateadd("yyyy",-1,date())

GRayL

Select Format([date-keyed],"yyyy" & "-" & "q") as Quarter where
[date-keyed] between date()-365 and date()

GRayL

Sorry, to be proper:

Select Format([date-keyed],"yyyy" & "-" & "q") as Quarter where
[date-keyed] between dateadd("yyyy",-1,date()) and date()

chrisamuel

ASKER

but that doesn't give me only the current 4 quarters---is that even possible?

GRayL

The dateadd() function will even take care of Leap Years.

chrisamuel

ASKER

I think I'm saying it wrong - what I mean is the current rolling 4 quarters so right now I should see some of 2004 data too so I really can't limit it to just this year

jkorz

where (DateDiff("q", [date-keyed], date()) <=4)

Nestorio

Then change the criteria to this:

date_keyed >= dateadd("m",-9,date())

GRayL

My select query will calculate the quarter for every record where the date is between now and a year ago.

ASKER CERTIFIED SOLUTION

jkorz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

You would of course include other fields in the Query. I also noticed [date-keyed] should be [date_keyed]. You should really avoid using special characters and spaces in field and table names. You must then wrap them in square brackets - not easy for us poor typists. DateKeyed is just as informative as date_keyed and a lot easier to create.

chrisamuel

ASKER

still getting too many quarters - only 4 rolling quarters, so since it's may, I would get 2 quarters from the end of last year and the 2 beginning quarters of this year including the one we are in right now

jkorz

I plugged this into my app and it worked like a charm

SELECT Format([date_keyed],"yyyy" & "-" & "q") AS Quarter
WHERE (DateDiff("q", [date_keyed], date()) <=3)

chrisamuel

ASKER

i think year needs to be added to this so i don't get 2003 but not sure where to put it
date_keyed >= dateadd("m",-9,date())

chrisamuel

ASKER

SELECT Format([date_keyed],"yyyy" & "-" & "q") AS Quarter
WHERE (DateDiff("q", [date_keyed], date()) <=3)

gives me
Quarter
2003-4
2004-1
2004-2
2004-3
2004-4
2005-1
2005-2

jkorz

Chrisamuel:

the big problem with this line:

date_keyed >= dateadd("m",-9,date())

is that it will not start on july 1 of 2004 but rather july 13 2004

jkorz

That is really odd... I get

2004-3
2004-4
2005-1
2005-2

GRayL

I see it now. Try:

Select Format([date-keyed],"yyyy" & "-" & "q") as Quarter where
[date-keyed] between dateserial(year(date())-1,(format(date(),"q"))*3,1) and date()

chrisamuel

ASKER

had an Or statement that was throwing it off - sorry

GRayL

Sorry, that should be:

Select Format([date_keyed],"yyyy" & "-" & "q") as Quarter where
[date_keyed] between dateserial(year(date())-1,(format(date(),"q"))*3,1) and date()

Darned underscores!!

jkorz

whew... I am not crazy... lol

glad I could help

GRayL

I assume there are no entries past date().

GRayL

jkorz: Nice post!